Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
User | Count |
---|---|
101 | |
89 | |
82 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |