Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello all,
I hope you are fine.
I am currently struggling with a formula in PowerBI and I need some help.
What I try to achieve is to compute the cumulative total of my data. However, my data are year-to-date data - coming from a source i can't modify.
So far, I am able to compute the cumulative but the trouble is that it's not responding to the minimum year I select.
Example :
My table has data from 2010 until 2020.
If I select the period 2010-2020, it's working fine. But I select 2012-2020 in a slicer, I got the same result, it doesn't take account the "year floor". And I have no idea how to solve that - already tried a few things.
Here is my formula, in case somebody more skilled than I pass around.
Solved! Go to Solution.
Dear Janey,
Thanks for your help.
However, It's not working either when adding this filter.
But, I have realized that when I use in the slicer the "YEAR" from the table [Vision] instead of the "YEAR" from [Calendar], the formula is working fine.
Which is "half" a solution since I prefer to use the YEAR from the calendar.
Thanks for your help,I will however keep this in mind for another time I guess.
Best regards,
Guillaume
Hi, @Anonymous
I checked your formula, The limitation on date above is only max, which of course will not work for the small year you choose.
You need to add a filter: 'Calendar'[Year] <= MIN ( VISION[YEAR] ) in your Cumulative_N_1 and Q1-Q4_N.
Like this:
Cumulative Total =
VAR TOTAL =
CALCULATE (
[EURO/LCBU],
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[DATE] )
),
'Calendar'[Quarter] = 4
)
VAR Cumulative_N_1 =
CALCULATE (
[EURO/LCBU],
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[DATE] )
),
'Calendar'[Quarter] = 4,
'Calendar'[Year] < MAX ( VISION[YEAR] ),
'Calendar'[Year] <= MIN ( VISION[YEAR] )
)
VAR Q1_N =
CALCULATE (
[EURO/LCBU],
'Calendar'[Quarter] = 1,
'Calendar'[Year] = MAX ( VISION[YEAR] ),
'Calendar'[Year] <= MIN ( VISION[YEAR] )
)
VAR Q2_N =
CALCULATE (
[EURO/LCBU],
'Calendar'[Quarter] = 2,
'Calendar'[Year] = MAX ( VISION[YEAR] ),
'Calendar'[Year] <= MIN ( VISION[YEAR] )
)
VAR Q3_N =
CALCULATE (
[EURO/LCBU],
'Calendar'[Quarter] = 3,
'Calendar'[Year] = MAX ( VISION[YEAR] ),
'Calendar'[Year] <= MIN ( VISION[YEAR] )
)
VAR Q4_N =
CALCULATE (
[EURO/LCBU],
'Calendar'[Quarter] = 4,
'Calendar'[Year] = MAX ( VISION[YEAR] ),
'Calendar'[Year] <= MIN ( VISION[YEAR] )
)
RETURN
IF (
MAX ( 'Calendar'[Quarter] ) = 1,
Cumulative_N_1 + Q1_N,
IF (
MAX ( 'Calendar'[Quarter] ) = 2,
Cumulative_N_1 + Q2_N,
IF (
MAX ( 'Calendar'[Quarter] ) = 3,
Cumulative_N_1 + Q3_N,
IF ( MAX ( 'Calendar'[Quarter] ) = 4, TOTAL )
)
)
)
Dear Janey,
Thanks for your help.
However, It's not working either when adding this filter.
But, I have realized that when I use in the slicer the "YEAR" from the table [Vision] instead of the "YEAR" from [Calendar], the formula is working fine.
Which is "half" a solution since I prefer to use the YEAR from the calendar.
Thanks for your help,I will however keep this in mind for another time I guess.
Best regards,
Guillaume
Hi, @Anonymous
It's my fault, I wrote it wrong, I even wrote '>=' as '<='...😅
If you use the calendar date as the slicer, You should modify part of the related code of the calendar table.
According to your description, I probably understand your context and logic. You should have two slicers, year and quarter (calendar), and then you want to calculate the cumulative total of all previous years (you selected) plus the cumulative total from this year to the quarter you selected, right?
There maybe a simpler code method that doesn't need to be so complicated, but I don’t understand why you add the code: calendar[quarter]=4, Doesn’t this mean we can only calculate the accumulation of the fourth quarter of the previous year?
If you still have ideas, maybe I can help you streamline the code.
Hello Janey,
Thanks for your support. Unfortunately, that's still not working if I use the YEAR from the [CALENDAR] table.
The reason i add the code 'Calendar'[Quarter]=4 is that the data are on a YTD Basis.
So in order to get the right cumulative of a Year I need to add only the previous last quarter (don't know if i am clear here).
Regarding the sens of the <, i had corrected it but it doesn't change anything, I still need to select the YEAR from [VISION] in order to have the right cumulative.
Anyway, I think i can close this topic and select "Solve" since i succeed to get what I wnat - even if not using the slicer i want to use. Will try modifying the full formula in order to use the CALENDAR table instad of VISION one to see how it works.
Thanks for your help !
Now i go and open a new topic about the merging of to calculated tables ...
@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
The idea is to create one column to have monthly or quarterly data and then use cumulative or YTD logic has per need
User | Count |
---|---|
131 | |
71 | |
70 | |
58 | |
54 |
User | Count |
---|---|
194 | |
95 | |
65 | |
62 | |
53 |