Forum Discussion
Date change compare based on data version
Hi All,
I am very new to Power BI, but a long-time excel user. I have spent a good deal of time reading and watching videos trying to solve what I think should be pretty straightforward, but no luck so far. My dataset is rather large, but I believe this simplistic view will capture the basics of what I need.
Essentially, I have the production start schedule of a bunch of tools (ID). I need to be able to see how those start schedules change based on the data version (instance) I pull.
The data version (instance) varies but is never more than one version per day. In Power BI I am controlling the Instance I see with a slicer. Here is a model of how the Table1 data is set up:
Table1
| ID | Instance | StartSchedule | StartChange |
| 1234 | 1/1/2021 | 5/5/2021 | |
| 1234 | 2/1/2021 | 5/5/2021 | 0 |
| 1234 | 3/1/2021 | 6/1/2021 | 27 |
| 1234 | 4/1/2021 | 6/1/2021 | 0 |
| 1234 | 5/1/2021 | 6/8/2021 | 7 |
| 5001 | 1/1/2021 | 4/3/2021 | |
| 5001 | 2/1/2021 | 6/1/2021 | 59 |
| 5001 | 3/1/2021 | 5/8/2021 | -24 |
| 5001 | 4/1/2021 | 6/12/2021 | 35 |
| 5001 | 5/1/2021 | 6/12/2021 | 0 |
One possible scenario in my report, I only select 2 Instances in the slicers, 1/1/2021 and 5/1/2021 and I want to see how those have changed:
Table1
| ID | Instance | StartSchedule | StartChange |
| 1234 | 1/1/2021 | 5/5/2021 | |
| 1234 | 5/1/2021 | 6/8/2021 | 34 |
| 5001 | 1/1/2021 | 4/3/2021 | |
| 5001 | 5/1/2021 | 6/12/2021 | 70 |
Another possible scenario in my report, I select 3 Instances in the slicers, 1/1/2021, 3/1/2021 and 5/1/2021 and I want to see how those have changed - each compared the last StartSchedule:
Table1
| ID | Instance | StartSchedule | StartChange |
| 1234 | 1/1/2021 | 5/5/2021 | |
| 1234 | 3/1/2021 | 6/1/2021 | 27 |
| 1234 | 5/1/2021 | 6/8/2021 | 7 |
| 5001 | 1/1/2021 | 4/3/2021 | |
| 5001 | 3/1/2021 | 5/8/2021 | 35 |
| 5001 | 5/1/2021 | 6/12/2021 | 35 |
How would a measure look that compares the Instances selected (in the slicers) and calculates the differences between the consecutive StartSchedules based on the ID?
Thanks!
Matt
Hi, Anonymous
You can try the following methods.
New table:
Table 2 = VALUES('Table'[Instance])Measure:
StartChange = VAR N1 =SELECTEDVALUE ( 'Table'[StartSchedule] ) VAR N2 =FILTER ( ALL ( 'Table' ), 'Table'[ID] = MAX ( 'Table'[ID] ) && 'Table'[Instance] IN 'Table 2' ) VAR N3 =CALCULATE ( MAX ( 'Table'[StartSchedule] ), TOPN ( 1, FILTER ( N2, [Instance] < SELECTEDVALUE ( 'Table'[Instance] ) ), [Instance], DESC ) ) VAR N4 =IF ( ISBLANK ( N3 ), N1, N3 ) RETURN VALUE ( N1 - N4 )Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello v-zhangti , that is great, works exactly as expected! Is a great foundation for the problems I am looking to solve.
Thanks!
7 Replies
Hi, Anonymous
You can try the following methods.
New table:
Table 2 = VALUES('Table'[Instance])Measure:
StartChange = VAR N1 =SELECTEDVALUE ( 'Table'[StartSchedule] ) VAR N2 =FILTER ( ALL ( 'Table' ), 'Table'[ID] = MAX ( 'Table'[ID] ) && 'Table'[Instance] IN 'Table 2' ) VAR N3 =CALCULATE ( MAX ( 'Table'[StartSchedule] ), TOPN ( 1, FILTER ( N2, [Instance] < SELECTEDVALUE ( 'Table'[Instance] ) ), [Instance], DESC ) ) VAR N4 =IF ( ISBLANK ( N3 ), N1, N3 ) RETURN VALUE ( N1 - N4 )Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello v-zhangti , that is great, works exactly as expected! Is a great foundation for the problems I am looking to solve.
Thanks!
Hi Anonymous ,
Try the following measure:
StartChange Measure = IF ( ISBLANK ( SUM ( 'Table'[StartChange] ) ), "", SUMX ( TOPN ( 2, FILTER ( ALL ( 'Table'[ID], 'Table'[Instance], 'Table'[StartChange] ), 'Table'[Instance] <= MAX ( 'Table'[Instance] ) && 'Table'[ID] = MAX ( 'Table'[ID] ) ), 'Table'[Instance], DESC ), 'Table'[StartChange] ) )Only using the IF statement because you have values that are blank and you want to present them in matrix.
Thank you Miguel!
I am struggling with this a bit. Correct me if I am wrong, but the 'Table'[StartChange] should actually be 'Table'[StartSchedule], correct? After I fix that, I get a value, but it is in the format of a date, like "4/21/2144", instead of 0 or empty if the dates didn't change. I checked the formate of the Measure and it is set to whole number. I checked both 'Table'[Instance] and 'Table'[StartSchedule], they are both set to short date. What am l missing?
Thanks,
Matt
Hi Anonymous,
I have copied your data to power BI and the column start change was the one with the whole numbers so was the one that I summed.
Maybe I have made the incorrect calculation but was the copy paste that you place on the initial post.
Can you share a print screen of the raw data so I can compare with what I did and give you the changes that are needed.
Hi MFelix ,
here is the raw data:
The "StartChange" is what I want the measure to do. For example, 1234 Instance 3/1/21 vs. 2/2/21 is 27 days reviewing the StartSchedule difference. Or for 5001 Instance 5/1/21 vs. 2/1/21 would be 11 days reviewing the StartSchedule difference.
Does that make sense?
Thanks again!
Matt
