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.
With Direct Query, some built in functions are no longer working.
I need a measure for previous month, previous quarter, previous year... to calcualte variance and growth. These measure should consider the previous month, year that falls outside of the slicer filter as well.
Here are my formulas for Current Month CM and Priort Month PM:
Trans Accounts CM = CALCULATE([Trans Accounts],FILTER(ALL(DimDate), DimDate[FirstDayOfMonth] = max(DimDate[FirstDayOfMonth])))
Trans Accounts PM = CALCULATE([Trans Accounts CM],DATEADD(DimDate[FirstDayOfMonth],-1, MONTH))
How do I change the PM formula to consider the previous month that falls before the date slicer?
Solved! Go to Solution.
Hi @steph_io,
If I understand you correctly, you should be able to follow steps below to get your expected result.
1. Use the formula below to add new calculate column in your DimDate table.
YearMonth = YEAR ( DimDate[FirstDayOfMonth] ) * 12 + MONTH ( DimDate[FirstDayOfMonth] )
2. Then you should be able use the formula below to calculate the Trans Accounts PM.
Trans Accounts PM = CALCULATE ( [Trans Accounts], FILTER ( ALL ( DimDate ), DimDate[YearMonth] = MAX ( DimDate[YearMonth] - 1 ) ) )
Regards
Hi @steph_io,
If I understand you correctly, you should be able to follow steps below to get your expected result.
1. Use the formula below to add new calculate column in your DimDate table.
YearMonth = YEAR ( DimDate[FirstDayOfMonth] ) * 12 + MONTH ( DimDate[FirstDayOfMonth] )
2. Then you should be able use the formula below to calculate the Trans Accounts PM.
Trans Accounts PM = CALCULATE ( [Trans Accounts], FILTER ( ALL ( DimDate ), DimDate[YearMonth] = MAX ( DimDate[YearMonth] - 1 ) ) )
Regards
This also resolves the issue I had with my calculations not looking at prior month when it fell in prior year. My formula wasn't working for each january. Thank you!
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 |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |