Forum Discussion
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:
| 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!
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
4 Replies
- Greg_Deckler
Community Champion
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 - amitchandak
Super User
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] 
v-deddai1-msftCommunity Support
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
