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. 

 

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.