Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
In my report, there is a report-level filter on the current month. I have to show the values of Amount for each category side by side for 2 different time period: first for the current month and for 2 months prior. Below are the two tables:
DimCategory
Key | Category |
1 | ABC |
2 | DEF |
3 | GHI |
FactCategory
Key | Amount | Date |
1 | 10 | 09/01/2017 |
2 | 20 | 09/01/2017 |
2 | 30 | 09/11/2017 |
3 | 40 | 09/01/2017 |
1 | 50 | 10/01/2017 |
2 | 60 | 10/02/2017 |
3 | 70 | 10/11/2017 |
1 | 80 | 11/15/2017 |
2 | 90 | 11/16/2017 |
3 | 80 | 11/17/2017 |
3 | 70 | 11/18/2017 |
I tried writing measure to calculate two months prior Amount for each category but the result I am getting is the sum of all category for that time period.
Measure = CALCULATE(SUM(FactCategory[Amount]),
FILTER(ALL(FactCategory), MONTH(FactCategory[Date]) = 9)) Measure 2 = SUMX( SUMMARIZE( FactCategory, DimCategory[Category], "VALUE", CALCULATE(SUM(FactCategory[Amount]), FILTER(ALL(FactCategory), MONTH(FactCategory[Date]) = 9)) ), [VALUE] )
How can I get the below output:
Category | Amount | Amount 2 months prior |
ABC | 80 | 10 |
DEF | 90 | 50 |
GHI | 150 | 40 |
Solved! Go to Solution.
Hi @abhay03,
You can try to use below formula to get previous data.
Measure:
Previous 2 Month = VAR current_Date = MAX ( FactCategory[Date] ) RETURN SUMX ( FILTER ( ALL ( FactCategory ), [Key] = MAX ( DimCategory[Key] ) && FORMAT ( [Date], "yyyy mmm" ) = FORMAT ( DATE ( YEAR ( current_Date ), MONTH ( current_Date ) - 2, 1 ), "yyyy mmm" ) ), [Amount] )
Regards,
Xiaoxin Sheng
Hi @abhay03,
You can try to use below formula to get previous data.
Measure:
Previous 2 Month = VAR current_Date = MAX ( FactCategory[Date] ) RETURN SUMX ( FILTER ( ALL ( FactCategory ), [Key] = MAX ( DimCategory[Key] ) && FORMAT ( [Date], "yyyy mmm" ) = FORMAT ( DATE ( YEAR ( current_Date ), MONTH ( current_Date ) - 2, 1 ), "yyyy mmm" ) ), [Amount] )
Regards,
Xiaoxin Sheng
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 | |
101 | |
84 | |
79 | |
69 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |