Forum Discussion

JBeyers's avatar
JBeyers
Advocate 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
Responsive Resident
10 years ago

Hi konstantinos Cheers - I want to show all the years on the same graph. One line for each different year. The axis is our season (just a financial year - 1st July to 30th June)

 

Cheers.

konstantinos's avatar
konstantinos
Memorable Member
10 years ago

elliotdixon An approach ( maybe not the best one - but I don't work much with fiscal dates ) maybe

 

YTD Current Fiscal = TOTALYTD(SUM(Sales[Amount);'Dates'[Date];"6-30")   - The last one is for specifying the end of year ( Fiscal )

Previous YTD Fiscal = CALCULATE([YTD Current Fiscal];DATEADD('Dates'[Date];-1;YEAR))

2 Years Before YTD =  CALCULATE([YTD Current Fiscal];DATEADD('Dates'[Date];-2;YEAR))

 

This way you can have any fiscal year dates in axis and compare all of them..Also can slice on Years

axis = fiscal year 2015  will show also 2014 & 2013 

axis = fiscal year 2013  will show also 2012 & 2011  

 

Hope it works..