Trailing 12 Months to work with other visuals

[Lmc_08]

Hi,

I've been working on a trailing 12 months line graphy which is on the same report page as other visuals which need to be filtered to the date level. I've managed to get the trailing 12 months graph to work using a disconnected table, but this runs into the issue of needing two date slicers. What I would like to see is if I select e.g. 31st December 2019, the line graph shows the values for the previous 12 months, and the other visuals to show the value for just the 31st December. Is this possible?

[v-deddai1-msft · Accepted solution]

Hi @Lmc_08 ,

You can create an inactive relationship between your disconnected table with your fact table and use USERELATIOSHIP function in your formula.

For the measure you wan to shows the values for previous 12 months before the selected date:

Measure for previous 12 months =
VAR A =
    MAX ( 'Table'[Date] )
VAR B =
    DATEADD ( 'Table'[Date], -12, MONTH )
RETURN
    CALCULATE (
        SUM ( 'Fact'[Value] ),
        FILTER ( 'Fact', 'Fact'[Date] <= A && 'Fact'[Date] >= B )
    )

For the measure you wan to shows the values for the selected date:

Measure for Selected date =
VAR A =
    MAX ( 'Table'[Date] )
VAR B =
    DATEADD ( 'Table'[Date], -12, MONTH )
RETURN
    CALCULATE (
        SUM ( 'Fact'[Value] ),
        USERELATIONSHIP( 'Table'[Date] ,'Fact'[Date]  )
    )

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

Best Regards,

Dedmon Dai

[amitchandak]

@Lmc_08 , I think have to follow the approach you have taken,

refer is there is anything different here: https://www.youtube.com/watch?v=duMSovyosXE