Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

calculate difference between date vs date -1 & sum the difference

Sorry I'm new to PowerBI and after few hours trying still could not figure out a right way to deal with this: I got data like this: Should CALCULATE(SUM())-CALCULATE(SUM()) be used? Date S...
Need Help
Tips and Tricks
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Anonymous ,

     

    >>1) calculate the difference between Date vs. Date -1

     

    Please refer to the dax code for calculated column below:

     

     

    DIFF =

IF (

    DATEADD ( 'Table'[Date], -1, DAY ) IN VALUES ( 'Table'[Date] ),

    VAR C = 'Table'[Some numbers]

    VAR Y =

        CALCULATE (

            SUM ( 'Table'[Some numbers] ),

            FILTER (

                'Table',

                'Table'[Date]

                    = EARLIER ( 'Table'[Date] ) - 1

                    && 'Table'[Set] = EARLIER ( 'Table'[Set] )

            )

        )

    RETURN

        C - Y,

    BLANK ()

)

     

     

    >>2) Sum up different of a Set, A = 10+10 = 20, B = -10+10 = 0

     

    You can create a measure to compute total of different:

     

     

    Measure = SUM('Table'[DIFF])

     

     

     

     

    For more details ,please refer to the pbix file : https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ESNDZuuwR_BAmuiwUDZArmcBY6-bho5CUD6OOrDbHivc3Q?e=FWPGIC

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

Well, the first part should be something like:

 

Column =
  Var __Today = [Some numbers]
  VAR __Yesterday = 
    SUMX(
      FILTER(
        'Table',
        [Date]=(EARLIER([Date])-1)*1. &&
          [Set] = EARLIER([Set])
      )
      ,[Some numbers])
RETURN
  __Today - __Yesterday