Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
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,

 

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

View solution in original post

5 REPLIES 5
v-janeyg-msft
Community Support
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 )
            )
        )
    )
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.
 
Best Regards,
Community Support Team _ Janey

 

Anonymous
Not applicable

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.

vjaneygmsft_2-1640830565844.png

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?

vjaneygmsft_1-1640830530904.png

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.

The_Guibu_0-1640858774524.png

 

Thanks for your help !
Now i go and open a new topic about the merging of to calculated tables ...

 

 

amitchandak
Super User
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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.