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.
Hi everyone
I'm stuck with a calculation, and i've been looking at the forums but i haven't found anything similar yet.
The database is as follows. I have 3 columns Year, id_Month and Number, and the data is monthly, from January(1) to December(12) for each year that goes from 2006 to 2016
i'm trying to calculate the increases or decreases for a chosen month, something like YTD formula, but the catch here is that because of the nature of this data, the total of a year isn't the sum of all the months but instead the data of December alone
So for example, if i want to calculate the variation of February 2016, the formula should go something like this:
(Sum of February 2016) minus (December 2015)
and so on for every February of the remaining years (Sum of February 2015 minus December 2014)
So i could have a stacked column chart by year in the X axis showing each february's variation
i found YTD and SamePeriodLastYear but i couldn't find a way, as i said, i don't need to sum every month for a given year to calculate the total of that year.
Also the idea is to have a filter in the canvas so i could choose the month i want to evaluate
Any ideas? also thx in advance for the help
Solved! Go to Solution.
Try this MEASURE
Measure = SUM ( TableName[Number] ) - CALCULATE ( SUM ( TableName[Number] ), FILTER ( ALL ( TableName ), TableName[id-Month] = 12 && TableName[YEAR] = SELECTEDVALUE ( TableName[YEAR] ) - 1 ) )
This worked perfectly!
just did some adjustments like adding a report filter for the first year since it will not have a previous year to calculate
Thank you so much!
Try this MEASURE
Measure = SUM ( TableName[Number] ) - CALCULATE ( SUM ( TableName[Number] ), FILTER ( ALL ( TableName ), TableName[id-Month] = 12 && TableName[YEAR] = SELECTEDVALUE ( TableName[YEAR] ) - 1 ) )
This worked perfectly!
just did some adjustments like adding a report filter for the first year since it will not have a previous year to calculate
Thank you so much!
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |