Slice display of Ever-to-Date measure (Running Total)

[Anonymous]

Hi,

I need to calculate the Running total for a certain measure, and then display only the Last 12 Months of that. 

For example:

Having the total Running Total by Date (line graph),  if filter date through a slicer to Year=2021 and Month=July what I need is the sum of this certain measure since August 2020. But in August 2020 the value should be the sum of all previous months.

Basically the approach I though was to calculate Running Total and then simply display the dates I want, but I don't know how to do that.

Appreciate any help 😀

[v-henryk-mstf]

Hi @Anonymous ,

Does your requirement mean that you want to obtain the value of [Valor PA] within the first 12 months from the date selected by the slicer.

I think you can try a formula similar to the following:

teste ETD = 
    VAR MaxDate = SELECTEDVALUE('Date'[Date])
    VAR Result =
        CALCULATE(
            [Valor PA],
            'Date'[Date] <= MaxDate && 'Date'[Date]>= MaxDate - 365,
            ALL('Date')
        )
    Return
        Result

If the problem is still not resolved, It is best to provide some test data (delete sensitive information). Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@Anonymous Have you tried the Running Total quick measure? Right-click a numeric column in the Fields pane, choose New quick measure.

[Anonymous]

@Greg_Deckler I can calculate the running total measure. I just can't display only the last 12 months of that measure and for the "earliest" month display the sum of previous months not displayed.

Am I explaining well? 

[Greg_Deckler]

@Anonymous Make your date slicer independent (disconnected). Then, in your running total measure just check the min and max of it and decide whether to return the value or BLANK.

[Anonymous]

@Greg_Deckler What if the min and max date to show depend on the date slicer choice?

[Greg_Deckler]

@Anonymous Right, that's what I said and why you make it independent.

Measure =

  VAR __Min = MIN('Slicer'[Date])

  VAR __Max = MAX('Slicer'[Date])

  VAR __Date = MAX('Table'[Date])

RETURN

  IF(__Date >= __Min && __Date <= __Max,<your running total measure>,BLANK())

[Anonymous]

@Greg_Deckler When you say MIN('Slicer'[Date]) and MAX('Slicer'[Date]), I don't understand very well. Imagine a slicer like this: 

Instead of MIN should I subtract 12 months to MAX(Slicer[Date])?

[Greg_Deckler]

@Anonymous I was thinking a date range slicer. You have to get this into date format. Yes, just use something like VAR __Min = EOMONTH(__Max,-12) or VAR __EOMONTH = EOMONTH(__Max,-11) VAR __Min = DATE(YEAR(__EOMONTH),MONTH(__EOMONTH),1)

Context, important for other things besides DAX...

[Anonymous]

@Greg_Deckler I'm sorry for the low context. I'm not getting what I need... I'll explain in detail.

This is my data model (the important part of it). These tables have both a date column and I use them to create a relantionship.

After that I created the ETD measure:

teste ETD = 
    VAR MaxDate = MAX('Date'[Date])
    VAR Result =
        CALCULATE(
            [Valor PA],
            'Date'[Date] <= MaxDate,
            ALL('Date')
        )
    Return
        Result

[Valor PA] is a simple measure to sum the sales.

It is showing the right result when using it in a Card Visual, but not in a Line Chart (probably due wrongly applied x-axis)

Also, I'm having trouble hidding the part that I don't want to display -> the so called Last12M_ETD.