Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I have souce table as below and i want output shown in image2.
As user want to see the difference of any two dates that is available in the source, so Please help me if there is any dynamic way to do this?
Source table:
Country | Received_Date (wk) | Amount |
IND | 2/5/2019 | 100 |
IND | 2/5/2019 | 150 |
AUS | 2/5/2019 | 120 |
AUS | 2/5/2019 | 180 |
CN | 2/5/2019 | 200 |
CN | 2/5/2019 | 90 |
IND | 2/12/2019 | 180 |
IND | 2/12/2019 | 345 |
AUS | 2/12/2019 | 234 |
AUS | 2/12/2019 | 566 |
CN | 2/12/2019 | 123 |
CN | 2/12/2019 | 575 |
IND | 2/19/2019 | 34 |
IND | 2/19/2019 | 687 |
AUS | 2/19/2019 | 56 |
AUS | 2/19/2019 | 534 |
CN | 2/19/2019 | 79 |
CN | 2/19/2019 | 23 |
image2 (output in power bi):
Country | 2/5/2019 | 2/12/2019 | Diff |
AUS | 300 | 800 | 500 |
CN | 290 | 698 | 408 |
IND | 250 | 525 | 275 |
OR (user selects the dates that he want to see Diff) |
Country | 2/12/2019 | 2/19/2019 | Diff |
AUS | 800 | 590 | -210 |
CN | 698 | 102 | -596 |
IND | 525 | 721 | 196 |
Note: i receive data every week and the received date will be in the source table.
Solved! Go to Solution.
Hi @Anonymous
You may create measures like below. Attached sample file for your reference.
Diff = VAR Max_Date = MAXX ( ALLSELECTED ( Table3 ), Table3[Received_Date (wk)] ) VAR Min_Date = MINX ( ALLSELECTED ( Table3 ), Table3[Received_Date (wk)] ) RETURN CALCULATE ( SUM ( Table3[Amount] ), Table3[Received_Date (wk)] = Max_Date ) - CALCULATE ( SUM ( Table3[Amount] ), Table3[Received_Date (wk)] = Min_Date )
Measure = IF ( SUM ( Table3[Amount] ) = SUMX ( ALLSELECTED ( Table3 ), Table3[Amount] ) || SUM ( Table3[Amount] ) = SUMX ( FILTER ( ALLSELECTED ( Table3 ), Table3[Country] = MAX ( Table3[Country] ) ), Table3[Amount] ), [Diff], SUM ( Table3[Amount] ) )
Regards,
Cherie
Hi @Anonymous
You may create measures like below. Attached sample file for your reference.
Diff = VAR Max_Date = MAXX ( ALLSELECTED ( Table3 ), Table3[Received_Date (wk)] ) VAR Min_Date = MINX ( ALLSELECTED ( Table3 ), Table3[Received_Date (wk)] ) RETURN CALCULATE ( SUM ( Table3[Amount] ), Table3[Received_Date (wk)] = Max_Date ) - CALCULATE ( SUM ( Table3[Amount] ), Table3[Received_Date (wk)] = Min_Date )
Measure = IF ( SUM ( Table3[Amount] ) = SUMX ( ALLSELECTED ( Table3 ), Table3[Amount] ) || SUM ( Table3[Amount] ) = SUMX ( FILTER ( ALLSELECTED ( Table3 ), Table3[Country] = MAX ( Table3[Country] ) ), Table3[Amount] ), [Diff], SUM ( Table3[Amount] ) )
Regards,
Cherie
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |