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

[Anonymous]

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	Some numbers	Set	Date vs Date -1 Difference
Mar 1	100	A	n/a
Mar 1	200	B	n/a
Mar 2	110	A	use Mar 2(110)-use Mar 1(100) = 10
Mar 2	190	B	use Mar 2(190)-use Mar 1(200) = -10
Mar 3	120	A	use Mar 3(120)-use Mar 2(110) = 10
Mar 3	200	B	use Mar 3(200)-use Mar 2(190) = 10

I want to do two things:
1) calculate the difference between Date vs. Date -1
2) Sum up different of a Set, A = 10+10 = 20, B = -10+10 = 0

A	20
B	0

And I will keep adding more data of set A & B in coming days.

Hope it's clear enough and I can learn new tricks from you! Thanks in advance!

[v-deddai1-msft · Accepted solution]

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_BAmuiwUD...

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

[Anonymous]

WORK LIKE A CHARM!
The sample file do help me understand the overall calcuation. Thanks a lot for spending time to create that tutorial!!

[amitchandak]

@Anonymous , You can create a new column like

diff = maxx(filter(table,table[set]=earlier(table[set])	 && table[Date]=earlier(table[Date])-1),table[Some numbers]) -table[Some numbers]

[Greg_Deckler]

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