Forum Discussion

Waseem's avatar
Waseem
Icon for Helper III rankHelper III
9 years ago
Solved

Calculating Cumulative Monthly Totals

Hello Power BI Gurus   I am stuck up with a situation, for which I have seen many solutions. However, nothing worked for me as I have more columns in my table. I need to calculate Monthly Cumulativ...
  • tringuyenminh92's avatar
    tringuyenminh92
    9 years ago

    Waseem, oh i'm sorry for missing in quickly typing. there is misssing filter in the expression:

     

    Cumulative_Actual =
CALCULATE (
    SUM ( 'TB'[Actual_KD] ),
   filter( ALL ( 'Dates' ),
    'Dates'[Date] <= MAX( 'Dates'[Date] ))
)

    please kindly try again with calculated measure

tringuyenminh92's avatar
tringuyenminh92
Icon for Memorable Member rankMemorable Member
9 years ago

Waseem, oh i'm sorry for missing in quickly typing. there is misssing filter in the expression:

 

Cumulative_Actual =
CALCULATE (
    SUM ( 'TB'[Actual_KD] ),
   filter( ALL ( 'Dates' ),
    'Dates'[Date] <= MAX( 'Dates'[Date] ))
)

please kindly try again with calculated measure

Waseem's avatar
Waseem
Icon for Helper III rankHelper III
9 years ago

tringuyenminh92 Many Thanks.

You made my day. Cheers

  • AndreSatziack's avatar
    AndreSatziack
    Icon for Helper I rankHelper I
    8 years ago

    Hello @Waseem

     

    I have the same problem, can you help me too? I used the same code, but this not worked for me :smileyfrustrated:

     

    Thank you very much ;)

    I have following table structure.

    • tringuyenminh92's avatar
      tringuyenminh92
      Icon for Memorable Member rankMemorable Member
      8 years ago

      Hi AndreSatziack,

       

      You should create Dates table by using Calendar() or CalendarAuto() method, and making relationship between your transaction table with this Dates table. Then apply above formula. please notice that we put filter on Dates table, not on transaction table.

       

      In case this is still not working, please share your current working file and i could quickly check it for you.

      • AndreSatziack's avatar
        AndreSatziack
        Icon for Helper I rankHelper I
        8 years ago

        Hello tringuyenminh92 and Waseem,

         

        Thank you for your help :)

         

        I tried to create but it did not work, it follows the same files I'm using to create the BurnDown graphic.

        Thanks again for the help :)

    • Waseem's avatar
      Waseem
      Icon for Helper III rankHelper III
      8 years ago

      Hi AndreSatziack

       

      You need to create a date table first and give it name "Date". You can create this table as below:

       

      Date = CALENDARAUTO()

       

      Then type following formula to crate a "New Measure":

       

       Cumulative = IF(COUNTROWS(Relatorio_Completo_2017)<0,CALCULATE(SUM(Relatorio_Completo_2017[Hours]),FILTER(ALL('Date'),'Date'[Date}<=MAX{'Date'[Date]))),BLANK())..

       

       

      Hope it helps

       

      Cheers