Forum Discussion

balumaran's avatar
balumaran
Frequent Visitor
9 years ago
Solved

Cumulative Sum With Slicer

Hi,  I am fairly new to Power BI and trying to find my ways in .    I have a requirement to display YTD value ( running sum) on a sales measure with a Month Slicer.  I was able to get the running ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Of course!

    mattbrice is right.

    In order to get YTD using ALL you need to use this:

    YTDSales = CALCULATE([Sales],FILTER(ALL('Business Date'),'Business Date'[Date]<MAX('Business Date'[Date])&&'Business Date'[Year]=YEAR(MAX('Business Date'[Date]))))

    or just use this staightforward formula:

    YTDSales = TOTALYTD([Sales],'Business Date'[Date])

     

    And make sure you use Date column from your 'Business Date' table in slicer

    Michael

Anonymous's avatar
Anonymous
Not applicable
9 years ago

This should work:

YTDSales = TOTALYTD([Sales],CalendarTable[Date]) 

Also, make sure that you use your CalendarTable Month column in your slicer (NOT the Month from the Fact table)

Michael

  • balumaran's avatar
    balumaran
    Frequent Visitor
    9 years ago

    Thanks Michael for responding. 

     

    My YTD Sales measure formule is this : 

     

    YTD SALES$=
    CALCULATE (
    FACT[Sales $],
    FILTER (
    ALLSELECTED ( 'Business Date'[Date] ),
    'Business Date'[Date] <= MAX ( 'Business Date'[Date] )
    )
    )

     

    My Fact table granularity is at Date level. The Slicer has the month number. With all values selected in Slicer, the running Sum for YTD sales is displaying correctly where as when I select a month in the Slicer, the result is filtered to the month selected and YTD number/running sum number becomes total for the month. 

     

    I used both ALL or ALL selected in the above measure; both behaves the same way. 

     

    - M

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      balumaran 

      ALLSELECTED should not work.

       

      ALL - should:

      YTDSales = CALCULATE([Sales],FILTER(ALL('Business Date'),'Business Date'[Date]<MAX('Business Date'[Date])))

       

      or this:

       

      YTDSales = TOTALYTD([Sales],'Business Date'[Date]) 

       

      Also - make sure that you use a Month from your BusinessDate table in the slicer

      Michael

      • balumaran's avatar
        balumaran
        Frequent Visitor
        9 years ago

        Thanks Michael. Let me give it a try and get back to you. 

        - Mani

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hello Michael, 

    I have the same problem but with different structure. 

    I have two tables:
    Extraction table
    And a date table

    I want to calculate a Cumulative Sum of the business values of the pirate tickets according to the start date column.

    I have a slicer that contains the sprint ID.

    When the sprint ID is selected in the slicer, I'd like the abcissa axis of the graph to display all the days between the start and end of the sprint included, and the curve to represent the data.

    Here's the formula I use, which unfortunately doesn't add up the data.
    The formula just shows me the data per day.
    I'd like to accumulate this data per day.


    RunningTotalePirate =
    Var Sprintstart = calculate(FIRSTDATE('Extraction_fin_sprint'[DébutSprint]), ALLSELECTED ('Extraction_fin_sprint'))
    Var Length = 20

    Return
    If (Max(TestCalendar[Date]) = Sprintstart , 0,
    If (Max(TestCalendar[Date]) >= Sprintstart && Max(TestCalendar[Date])<= Sprintstart + Length,
    0 + Calculate(sum(Extraction_fin_sprint[Business Value]),
    Filter(allselected(Extraction_fin_sprint),
    Extraction_fin_sprint[Prévu_Pirate]= "pirate"),
    Extraction_fin_sprint[Start date] <= max(TestCalendar[Date])
    && Extraction_fin_sprint[Date départ] >= min(TestCalendrier[Date])
    )
    ))