Cumulative number of active jobs at the end of each month
I am trying to reflect the cumulative number of active jobs at the end of each month, excluding those that were completed during that month.
My current measure is not working, the idea behind the two separate Count adds result to account for jobs that are active due to either a future completion date or no completion date at all.
The Dimension I am using is Month-Year // Active Jobs at the End of Month
Count({
<
[Modified Discovered Date] = {"<=MonthEnd(Max([Month-Year]))"}, // Jobs discovered on or before the end of the month
[Modified Completion Date] = {">=MonthEnd(Max([Month-Year]))"} // Jobs not completed before the end of the month
>
} [JSN])
+
Count({
<
[Modified Discovered Date] = {"<=MonthEnd(Max([Month-Year]))"}, // Jobs discovered on or before the end of the month
[Modified Completion Date] = {"="} // Jobs with no completion date (still active)
>
} [JSN])
Sample Data:
JSN
Modified Discovered Date
Modified Completion Date
1
June 15, 2023
(blank)
2
June 20, 2023
July 15, 2023
3
July 1, 2023
(blank)
4
June 1, 2023
June 30, 2023
5
June 25, 2023
(blank)
Understanding the Logic: Uses the following fields:
JSN - identifies a job
Modified Discovered Date - Identifies when I job or JSN is created and active
Modified Completion Date - identifies when a job is complete, no modified completion date means the job is still active
Active Jobs: Jobs are counted as active from their discovery month up to the month before they are completed.
Completed Jobs: Jobs are marked as completed only in the month they were completed.
At that link you will find some code and an example Sense app you can download and see the technique working.
The biggest difference is that the example is by week and you want by month. You will just need to add the following line underneath the WeekStart line:
Date(MonthStart([Month-Year]), 'MMM-YYYY') as Month,
Slightly off topic, but in the set analysis code you have in your post you are missing $(=...) around the expression you are trying to match to. The dollar sign expansion allows code to be executed outside of the statement and then be injected back in, thus:
Note also that for set analysis to work, the date format must match exactly the native format of the date field you are comparing to, so a date function is often required.
