DAX for difference between each date and previous date per occurrence

Water — Fri, 27 Oct 2023 20:24:45 GMT

Hi,

Please help with DAX to accomplish the following?

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 2020-06-17) = 371 (days). In the same way, 2022 July should display (2022-07-05 minus 2021-06-23) = 377 (days).

The ideal solution should like like the below showing number of days:

All the data is captured in the same WO WITH JOB DETAIL table. Here sample data from this table: Sample dataset . The data captured in Power BI is here. Not sure if I should add a Date/Calendar table. This Power Bi file contains a date table.

You will note the interval between the various jobs differs.

One would typically filter on a specific vehicle to monitor service intervals for its many jobs.

With thanks,

Water

Re: DAX for difference between each date and previous date per occurrence

lbendlin — Fri, 06 Oct 2023 22:36:01 GMT

This is not giving you what you asked for, but probably what you wanted.

Re: DAX for difference between each date and previous date per occurrence

Water — Fri, 27 Oct 2023 20:31:04 GMT

Thank you very much for an inbetween solution!

However, idealy I still hope for an output like the below in days difference:

Updated the Power BI file with a better date relatonships now that I understand that better.

Would you mind to please have another look?

With hope and thanks,

Water

topic DAX for difference between each date and previous date per occurrence in DAX Commands and Tips

DAX for difference between each date and previous date per occurrence

Re: DAX for difference between each date and previous date per occurrence

Re: DAX for difference between each date and previous date per occurrence