Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have been struggling with this DAX formula for months now and the application is now occurring in several different areas of our business. I have date stamped transactional data such as the below. You can see there are duplicate Job numbers to reflect every time the status was changed.
I would like to do a running count of jobs over time. My challenge is coming up with a formula that can perform the running calculation* and also filter out the older job number rows in the process**. I can do each separately but am struggling to marry the two together.
Any help would be appreciated.
Thanks in advance,
Jon
*running_total = CALCULATE( COUNT( [job number] ), FILTER( ALLSELECTED( 'Dates'[Date] ), 'Dates'[Date] <= MAX( 'Dates'[Date]) )))
**most_recent_jobs = FILTER( jobstatus, jobstatus[Change Date] = CALCULATE( MAX( jobstatus[Change Date]), ALLEXCEPT( jobstatus, jobstatus[Job Number])))
Job Number | Status | Change Date |
10051 | SCH | 3/27/2013 0:00 |
10051 | WPLN | 3/27/2013 0:00 |
10052 | EAPPR | 3/27/2013 0:00 |
10052 | INPRG | 3/27/2013 0:00 |
10052 | WENG | 3/27/2013 0:00 |
10051 | COMP | 3/28/2013 0:00 |
10051 | CLOSE | 4/10/2013 0:00 |
10052 | WI | 9/16/2014 0:00 |
10052 | CLOSE | 11/30/2015 0:00 |
100508 | WPLN | 3/11/2017 0:00 |
100508 | WSCHED | 3/11/2017 0:00 |
100508 | SCH | 3/14/2017 0:00 |
100508 | NO WORK | 4/25/2017 0:00 |
100508 | CLOSE | 5/15/2017 0:00 |
IF(max(jobstatus[Change Date])=CALCULATE( MAX( jobstatus[Change Date]), ALLEXCEPT( jobstatus, jobstatus[Job Number])),CALCULATE( COUNT( [job number] ), FILTER( ALLSELECTED( 'Dates'[Date] ), 'Dates'[Date] <= MAX( 'Dates'[Date]) )))))
User | Count |
---|---|
51 | |
36 | |
20 | |
15 | |
13 |
User | Count |
---|---|
94 | |
72 | |
29 | |
22 | |
13 |