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.
Afternoon,
I'm trying to calculate the percentage change between two concurrent months for example; I'm comfortable with the overall setup of that, but I'm not sure how to only do it for the final value of the month for the respective months.
I've tried EODMonth, LastDate, etc but I'm not sure how to only pull the final date of each month.
Solved! Go to Solution.
Hey,
here you will find a pbix file that contains a little example to answer your question.
Please be ware that the solution also relies on a Calendar table, related to the fact table. Besides the Date, Year-Month, and Year column, this table also contains a column that numbers the Months meaning that all days of the first month are marked with 1.
All the measures contained defined in the Calendar table are not necessary for the final calculation.
The fact table contains two measures: "growth MoM Latest Value" and "Final growth MoM Latest Value" the first one calculates everything in one go, whereas the 2nd one uses the interemediate measures "ms ... curr Month" and "ms ... prev Month".
Here is a litte screenshot that shows the base data
and here is a screenshot from a table that also contains all the measures
And here is one of the measures
growth MoM Latest Value = var factcurrentRunningMonth = MAX('Calendar'[runnning Month]) var factLatestDateCurrentMonth = CALCULATE( MAX('simple fact'[Date]) ,FILTER( ALL('Calendar') ,'Calendar'[runnning Month] = factcurrentRunningMonth ) ) var factLatestDatePrevMonth = CALCULATE( MAX('simple fact'[Date]) ,FILTER( ALL('Calendar') ,'Calendar'[runnning Month] = factcurrentRunningMonth -1 ) ) var latestvalueCurrMonth = CALCULATE( SUM('simple fact'[Amount]) ,FILTER( ALL('Calendar') ,'Calendar'[Date] = factLatestDateCurrentMonth ) ) var latestvaluePrevMonth = CALCULATE( SUM('simple fact'[Amount]) ,FILTER( ALL('Calendar') ,'Calendar'[Date] = factLatestDatePrevMonth ) ) return DIVIDE(latestvalueCurrMonth, latestvaluePrevMonth, BLANK())
Hope this is what you are looking for
Regards
Tom
Hey,
here you will find a pbix file that contains a little example to answer your question.
Please be ware that the solution also relies on a Calendar table, related to the fact table. Besides the Date, Year-Month, and Year column, this table also contains a column that numbers the Months meaning that all days of the first month are marked with 1.
All the measures contained defined in the Calendar table are not necessary for the final calculation.
The fact table contains two measures: "growth MoM Latest Value" and "Final growth MoM Latest Value" the first one calculates everything in one go, whereas the 2nd one uses the interemediate measures "ms ... curr Month" and "ms ... prev Month".
Here is a litte screenshot that shows the base data
and here is a screenshot from a table that also contains all the measures
And here is one of the measures
growth MoM Latest Value = var factcurrentRunningMonth = MAX('Calendar'[runnning Month]) var factLatestDateCurrentMonth = CALCULATE( MAX('simple fact'[Date]) ,FILTER( ALL('Calendar') ,'Calendar'[runnning Month] = factcurrentRunningMonth ) ) var factLatestDatePrevMonth = CALCULATE( MAX('simple fact'[Date]) ,FILTER( ALL('Calendar') ,'Calendar'[runnning Month] = factcurrentRunningMonth -1 ) ) var latestvalueCurrMonth = CALCULATE( SUM('simple fact'[Amount]) ,FILTER( ALL('Calendar') ,'Calendar'[Date] = factLatestDateCurrentMonth ) ) var latestvaluePrevMonth = CALCULATE( SUM('simple fact'[Amount]) ,FILTER( ALL('Calendar') ,'Calendar'[Date] = factLatestDatePrevMonth ) ) return DIVIDE(latestvalueCurrMonth, latestvaluePrevMonth, BLANK())
Hope this is what you are looking for
Regards
Tom
@TomMartensthanks for the quick repsonse. The latest day of each month.
So for example, for Feb 2018 it would be the 28/2. For this month it would be the 4th (Current date for me is the 4/4).
The same number of days don't have to be considered, I saw an example on radacad but this should be a little bit nicer.
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |