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

 

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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Great, this solution works, but what if you want to throw in a dimension at the legend level, this does not seem to work and is only multiplying and duplicating values. How can this formula be modified in the process?