Forum Discussion

birdie29's avatar
birdie29
Advocate II
10 years ago
Solved

Flexible Cumulative Sum

Hi All

 

I'm still very new to Power BI and DAX for that matter so I'm hoping someone can help me with my issue.

 

I essentially want to create a measure which shows the cumulative costs for the periods selected in my slicer.

 

For example, I have data from January to December but I have only the periods March to May selected on my slicer so on a graph I was hoping the March column will show just March costs and the April column the sum of March and April costs and so on. Is this possible or am I thinking illogically?

 

I'm currently using the measure in the attached screenshot however you'll notice in the table, on the first row dated 1st March it is showing a cumulative total that includes prior periods (£745,971). I would want this particular cell to show £1,047 then the following row £2,034 (£1,047+£987) and so on.

 

 

 

Hope this makes sense!

 

Thank you

 

Chris

  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    birdie29

     

    We can create a calendar table and use ALLSELECTED to get the expected result.

    If we have a table like below. We can create another new table with DAX formula like below.

    CalendarTable =
CALENDAR ( "1/1/2016", "12/31/2016" )

     

    Then create a measure with following formula. I’ve also uploaded my PBIX file here for reference.

    Running Total = 
CALCULATE (
    SUM ( Table1[Total Costs] ),
    FILTER (
        ALLSELECTED ( CalendarTable ),
        CalendarTable[Date] <= MAX ( CalendarTable[Date] )
    )
)

     

    Best Regards,

    Herbert

4 Replies

  • CahabaData's avatar
    CahabaData
    Memorable Member
    10 years ago

    Makes perfect sense, and I don't know the answer - which I'm sure is not what you want on the first reply post....

     

    You could change to a bar chart with the drill down feature - which I believe will recalc each level.

     

    But with a slicer as to whether the Cumulative formula will calc on just the sliced amounts - I myself would be interested to know if that is a feature selectable in the visual user interface or can be accomplished via code.

     

     

    • birdie29's avatar
      birdie29
      Advocate II
      10 years ago
      Thanks for replying!

      I'm glad to know someone else has the same question. It feels to me there should be a formula in DAX to do this however I just don't have the level of knowledge yet.
  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    10 years ago

    birdie29

     

    We can create a calendar table and use ALLSELECTED to get the expected result.

    If we have a table like below. We can create another new table with DAX formula like below.

    CalendarTable =
CALENDAR ( "1/1/2016", "12/31/2016" )

     

    Then create a measure with following formula. I’ve also uploaded my PBIX file here for reference.

    Running Total = 
CALCULATE (
    SUM ( Table1[Total Costs] ),
    FILTER (
        ALLSELECTED ( CalendarTable ),
        CalendarTable[Date] <= MAX ( CalendarTable[Date] )
    )
)

     

    Best Regards,

    Herbert

    • birdie29's avatar
      birdie29
      Advocate II
      10 years ago

      Thank you v-haibl-msft!

       

      The perfect solution and so much simplier than I thought it would be! Works a treat :)

       

      Thanks again

      Chris