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

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

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