cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

Cumulative Total based on YTD figures

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.

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]))
VAR Q1_N = CALCULATE([EURO/LCBU],'Calendar'[Quarter]=1,'Calendar'[Year]=max(VISION[YEAR]))
VAR Q2_N = CALCULATE([EURO/LCBU],'Calendar'[Quarter]=2,'Calendar'[Year]=max(VISION[YEAR]))
VAR Q3_N = CALCULATE([EURO/LCBU],'Calendar'[Quarter]=3,'Calendar'[Year]=max(VISION[YEAR]))
VAR Q4_N = CALCULATE([EURO/LCBU],'Calendar'[Quarter]=4,'Calendar'[Year]=max(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))))

Thanks for your help, and enjoy christmas !

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Dear Janey,

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

5 REPLIES 5
Community Support

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 )
)
)
)
``````

Best Regards,
Community Support Team _ Janey

Anonymous
Not applicable

Dear Janey,

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

Community Support

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.

Best Regards,
Community Support Team _ Janey
Anonymous
Not applicable

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.

Now i go and open a new topic about the merging of to calculated tables ...

Super User

@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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.