Forum Discussion

JBeyers's avatar
JBeyers
Icon for Advocate IV rankAdvocate IV
10 years ago
Solved

DAX: How to perform a cummulative summation

I was wondering how I should build a graph that looks like the following: I want to make a sum, and display it across all months. I can't do this with the "SUM" or "SUMX" function, because i...
Need Help
  • konstantinos's avatar
    konstantinos
    10 years ago

    First you need to have a seperate date table in order to perfom date calculations...

     

    Step 1 : Create a date table -> Go to Modelling click New Table -> enter   Dates = CALENDARAUTO()..Now you have a date table call Dates..-> New Column in this table  Month = MONTH(Dates[Date])

    3.png

     

    Step 2 : Create Relantionship between your   yourtable[Dates] ( the new only dates you created ) and Dates[Date] ( the calculated table)

    2.png

     

    Step 3 : Rewrite your formula to Measure = CALCULATE(
                             SUM(rprtsolarhistorical[Daily Output]);
                             FILTER(
                                             ALL(Dates[Dates]);
                                            Dates[Date]) <= MAX(Dates[Date])
                             )
                       )

     

    Step 4: add the months field from the new Dates table and the "measure"

     

    Hope this works

     

elliotdixon's avatar
elliotdixon
Icon for Responsive Resident rankResponsive Resident
10 years ago

Hi Haegi

Thanks for the help. I can create the measure with no errors but cannot then bring it into any table or graph??

Get an error
"MdxScript(Model) (2, 43) Calculation error in measure 'InvoiceDetail'[CumulativeYear]: The function MAX takes an argument that evaluates to numbers or dates and cannot work with values of type String."

My code is.

CumulativeYear = CALCULATE(SUM(InvoiceDetail[DETAIL_KMS_TOCHARGE]), 
FILTER(ALL(Dates[Year]),Dates[Year] = MAX(Dates[Year])),  
FILTER(ALL(Dates[MonthName]),Dates[MonthName] <= MAX(Dates[MonthName])))

Same result if I remove out the Month filters and just have one on the years.

Haegi's avatar
Haegi
Icon for Advocate V rankAdvocate V
10 years ago

Good job!

 

This error explain that MAX function doesnt work with String value.

I suppose the column 'MonthName' is a string like "January" or "Febuary", we must instead use integer value like 1,2,3,4,5 etc..

for the Month column.

 

Regards.