Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 luc...
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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's avatar
    Anonymous
    4 years ago

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

    Thanks!

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    MFelix
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        MFelix
        Super User
        4 years ago

        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?