Date change compare based on data version

[Anonymous]

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

[v-zhangti · Accepted solution]

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.

[v-zhangti · Accepted solution]

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.

[Anonymous · Accepted solution]

Hello @v-zhangti , that is great, works exactly as expected!  Is a great foundation for the problems I am looking to solve.

Thanks!

[Anonymous]

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

[MFelix]

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. 

[Anonymous]

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

[MFelix]

Hi @Anonymous,

Did not understood that the last column was the one you wanted to calculate. 

In this case and in order to increase the performance I would suggest create the column with the difference and the use a measure similar to mine to make the calcution based on selection. 

Do you want me to send the formula for the column or do you prefer to have everything calculated in a measure? 

[MFelix]

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.