Running Total

[Anonymous]

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]         )))

[amitchandak · Accepted solution]

@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 · Accepted solution]

@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

[Anonymous]

@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]

@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

[Ashish_Mathur]

Hi,

Try this measure

=calculate([Total savings],datesytd('Dates'[Date],"31/12"))

Hope this helps.

[Anonymous · Accepted solution]

@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

[Anonymous]

@Anonymous : I cracked it , the bold highlighted did the trick 

I wanted view the year and the number should start from the selected year so mention below did the trick 

Saving Running Total = CALCULATE([Total Savings],

FILTER(ALLSELECTED('Dates'[Date]) ,

'Dates'[Date] <= MAX('Dates'[Date]) && Dates[Date] >= MIN(Dates[Date]

)))

[v-lili6-msft · Accepted solution]

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

[Anonymous]

I accept this as solution 

[Anonymous]

@v-lili6-msft Yes, this did the trick and it helped, i was including the Date[Date] while Allselection, thanks to you and the whole team who helped me and i fixed the code while Allselecting only Date exactly like you stated

Saving Running Total 3 = CALCULATE([Total Savings],

FILTER(ALLSELECTED('Dates') ,

'Dates'[Date] <= MAX('Dates'[Date]) ))

[ryan_mayu]

@Anonymous 

please try

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

[amitchandak · Accepted solution]

@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"))