Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Running Total

Hi,

I have a running total measure which is calculating sinec inception i.e. I want the running total to be adjsuted according to the year i choose from the slicer.

When i choose the Year it get the figure from the previous month

For eg If i choose Jan 2020 it will bring the Total from Dec 2019.

Measure for Running total:
Saving Running Total test = CALCULATE([Total Usage Savings],
FILTER(ALL('Dates'[Date]) ,
'Dates'[Date] <= MAX('Dates'[Date]
        )))
  

 

 

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    6 years ago

    Anonymous , issue is no clear with description you can try

    Saving Running Total test = CALCULATE([Total Usage Savings],
    FILTER(ALLselected('Dates'[Date]) ,
    'Dates'[Date] <= MAX('Dates'[Date]
    )))

     

    for yearly you can also try ytd

    YTD Sales = CALCULATE([Total Usage Savings],DATESYTD('Date'[Date],"12/31"))

  • Anonymous's avatar
    Anonymous
    6 years ago

    Anonymous  You can get this working by using this.

     

    Test Running Total = 
     Calculate(
          [Total Savings],
          FILTER( ALLSELECTED(Dates),
                 'Dates'[Date] <= MAX(Dates'[Date])
          )
      )

     

    However, if your Dates table has future date this behaves strange.
    I couldn't crack that, this happens because of the ALLSELECTED. Seems like I need to revisit ALLSELECTED which is pretty tough to master.

    Perhaps Greg_Deckler or Anonymouscan explain this.

     

    Thanks and Regards

  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  Anonymous 

    For your case, this formula should work

    Saving Running Total 3 = CALCULATE([Total Savings],
    FILTER(ALLSELECTED('Dates') ,
    'Dates'[Date] <= MAX('Dates'[Date]) ))

     

    or

    TOTALYTD may meet your requirement too

    Saving Running Total 4 = TOTALYTD([Total Savings],Dates[Date])
     
    Regards,
    Lin

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , issue is no clear with description you can try

    Saving Running Total test = CALCULATE([Total Usage Savings],
    FILTER(ALLselected('Dates'[Date]) ,
    'Dates'[Date] <= MAX('Dates'[Date]
    )))

     

    for yearly you can also try ytd

    YTD Sales = CALCULATE([Total Usage Savings],DATESYTD('Date'[Date],"12/31"))

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    6 years ago

    Anonymous 

    please try

    = CALCULATE([Total Usage Savings],
    FILTER(ALL('Dates'[Date]) ,
    'Dates'[Date] <= EOMONTH(MAX('Dates'[Date],-1)
    )))

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous 

     

    Based on your description what I think is, you are trying to adjust your Inception Date  based on slicer selection right?

     

    In that case just replace ALL with ALLSELECTED in your measure.

     

    Hope this helps.

     

    Thanks

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous  Yes,  You got my requirement correct, Thank you It helped but the the running total number in Jan is still showing from the previsous month addition where i selected only 2020 it is still adding from the previous month Savings, 

       

      I want, if i select 2020 it should do the calculation just like total saving and start from the Jan 2020

       


      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Anonymous  You can get this working by using this.

         

        Test Running Total = 
     Calculate(
          [Total Savings],
          FILTER( ALLSELECTED(Dates),
                 'Dates'[Date] <= MAX(Dates'[Date])
          )
      )

         

        However, if your Dates table has future date this behaves strange.
        I couldn't crack that, this happens because of the ALLSELECTED. Seems like I need to revisit ALLSELECTED which is pretty tough to master.

        Perhaps Greg_Deckler or Anonymouscan explain this.

         

        Thanks and Regards