Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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_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
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
WORK LIKE A CHARM!
The sample file do help me understand the overall calcuation. Thanks a lot for spending time to create that tutorial!!
@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]
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |