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 |
