Background: For each vehicle many jobs are performed at various date intervals. Each time a job is performed, the date (JOB_OPEN_DATE) is captured. Below in the table you can see the JOB_OPEN_DATEs when different jobs in the JOB column were performed at various dates for one selected vehicle. (There are of course many more vehicles.)
Question: I don’t want to display the actual date when each job was performed as you can see in the below table, but rather the difference between each date and the previous date for each job - in number of days. This is to see clearly the interval (in days) at which the jobs were performed. This means e.g. for the first Job 06-02-005, 2021 June should display (2021-06-23 minus the previous 2020-06-17) = 371 (days). In the same way, 2022 July should display (2022-07-05 minus the previous 2021-06-23) = 377 (days).
All the data is captured in the same WO_JOBS table. Here is sample data from this table.
The data captured in Power BI here.
PLEASE NOTE: the interval between the various jobs differs. Some happen every two weeks, others once a year, and everything in between. The DAX should allow display/drill-down to at least week level, and not only month level. (Jobs ending with BIW are bi-weekly jobs.)
One would typically filter on a specific vehicle to monitor service intervals for its many jobs.
William
I added a column to calculate the difference between two dates ( JOB open date )
taking into account the JOB and unit NO
of course you can change it into a measure if you like
attached the Pbix
Thank you so very much, this is the most amazing solution! As a calculated column it works perfectly. After struggling so long to find a solution I can tell you I feel a bit emotional!
Three questions if I may:
I see you say I can change it to a measure. Would that be preferable even if the calculated column is already performing as I hoped?
If there is no good reason to change it to a measure, ignore this question no 2: I tried to create a new measure by copying the code over as a new measure, but then get the error message: “EARLIER/EARLIEST refers to an earlier row context which doesn’t exist.” Not sure what I could be doing wrong. Do I need to change anything in the code moving from calculated column to a measure?
Then lastly, your elegant solution provides as asked the time difference in days between the job open dates for when jobs are performed. I posted a different question here where I ask for the difference in mileage to be displayed. Is it possible to use this same solution in this post (for days difference) in my other post for the mileage (Meter_KM) difference? If yes, please help me with the how? I hope this would be possible, as this seems such an elegant solution!
With (you have no idea with how much) great appreciation,
