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

Hi Waseem,
Instead of using Calculated Column, you could use Calculated Measure:

  • Created new Dates table: Dates= Calendarauto()
  • Making relationship between fact and dates table
  • Create calculated measure:
Cumulative_Actual =
CALCULATE (
    SUM ( 'TB'[Actual_KD] ),
    ALL ( 'Dates'),
    'Dates'[Date] <= MAX ( 'Dates'[Date] )
)

Please refer my example as a part of topic: https://community.powerbi.com/t5/Desktop/DAX-Count-of-Stores-that-are-under-the-Average/td-p/100685

 

In case you still want to go on with Calculated Column, you could try replace method ALL with ALLEXCEPT(TB,columnyouwantfilter1,columnyouwantfilter2)

 

Please feel free to show your expectation in picture or let me know if you need a sample to clarify any concern. If this works for you please accept it as solution and also like to give KUDOS. 

 

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

    Hi tringuyenminh92

     

    Thanks a lot for your prompt response. I tried to do what you suggested but there was an error prompt. I created both a measure and a column but ended up with same error message. Below is the snapshot of my dashboard. Appreciate your help.

     

     

     

     

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

    Hi tringuyenminh92

     

    Sorry if it is not legible. The error reads like following:

     

    "A Function MAX has been used in the True/False expression that is used as a Table Filter expression. This is not allowed".

     

    Regards

    • 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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank you so much for this input that create measure not column to get the desired result. :) Finally, this got my work done.