Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
55 | |
25 | |
23 | |
14 | |
11 |
User | Count |
---|---|
78 | |
63 | |
47 | |
17 | |
12 |