How to display curve of the last 12 months for a measure after selecting a month on a slicer

[Anonymous]

Hi All, 

I am faced with a challenge. I have a page with many visuals and a year and month filter. I want to add another visual on this page that when the user select a month (e.g Oct ) and year (e.g 2019), the visual should display a curve from Oct 2018 to Oct 2019.

My measure is: total_end =  sum(interruption([END])

I have a date dimension which is connected to interruption fact table.

The problem I have right now is that when I select Oct 2019. The curve only display a single point "oct 2019" 

What can I do to solve this ?

[Anonymous · Accepted solution]

I ended up solving the problem by creating the measure below:

end_last_n_months = 
CALCULATE(
    [total_end],
    DATESINPERIOD(DimDate[Date], MAX(DimDate[Date] ),-12, MONTH )
	)

and then on my axis, I used the date column from the facts table.

[Mariusz]

Hi @Anonymous 

You need Two Date Dimensions / Calendar table to achieve your requirement.
The below measure needs to be used in the visual you want to display 12 months.

I've attached the file with the example.

Measure = 
VAR _drs = MAX( Dates[Date] )
VAR _dre = DATE( YEAR( _drs ), MONTH( _drs ) -12, DAY( _drs ) )
RETURN 
CALCULATE(
    SUM( 'Table'[Value] ),
    ALL( Dates ),
    FILTER( 
        'Dates ( second )',
        AND( 
            'Dates ( second )'[Date] <= _drs,
            'Dates ( second )'[Date] >= _dre
        )
    )
)

Let me know how you get on.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.