Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 !
 
 
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        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