Multiple date filters and evaluations

[Nikolainoergard]

Hi community

I have a Summary dashboard that should include actual figueres from the selected period, but also include LTM and PLTM figueres based on the same date slicer.

I followed this great article by SQLBI in terms of data model etc.:  https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/, and it only works partly for my needs.
My calc item:

VAR NumberOfMonths = 12
VAR ReferenceDate =
    MAX ( 'pbi DimDate'[Calendar_ConsecutiveFiscalPeriod] )
VAR PreviousDates =
    CALCULATETABLE (
        'Previous Date',
        'Previous Date'[Calendar_ConsecutiveFiscalPeriod] > ReferenceDate - NumberOfMonths
            && 'Previous Date'[Calendar_ConsecutiveFiscalPeriod] <= ReferenceDate
    )
VAR Result =
    CALCULATE (
        SELECTEDMEASURE (),
        REMOVEFILTERS ( 'pbi DimDate' ),
        KEEPFILTERS ( PreviousDates ),
        USERELATIONSHIP ( 'pbi DimDate'[Date_Key], 'Previous Date'[Date_Key] )
    )
RETURN
    Result

I have two issues:
1.  When applying the Previous Date table and Calc item to the visusal, it returns sales in actual for period and not in LTM aggregation.
2.  The calc. item dosn't support to show both my LTM and PLTM measure on the visual.

This means the Previous date table with relationship works, becuase when i filter on the main date table, it will use that value as the max, and then show last 12 months. 

My LTM calculation: (PLTM is same logic but -12 on _MaxPeriod)

Sales LTM = 
VAR _MaxPeriod = MAX('pbi DimDate'[Calendar_ConsecutiveFiscalPeriod])
VAR _MinPeriod = _MaxPeriod - 12
VAR _Result =
    CALCULATE(
        [Sales], 
        REMOVEFILTERS('pbi DimDate'),
            'pbi DimDate'[Calendar_ConsecutiveFiscalPeriod] <= _MaxPeriod &&
            'pbi DimDate'[Calendar_ConsecutiveFiscalPeriod] > _MinPeriod
    )
RETURN
    _Result

Any suggestions on how to fix the two errors?

Thanks in advance

[amitchandak]

@Nikolainoergard , You can try measure like this with help from a date/calendar table

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH))

Rolling 12 = CALCULATE([Net], WINDOW(-11,REL, 0, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

PLTM

Rolling 12 before 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],eomonth(MAX('Date'[Date]),-12),-12,MONTH))

Rolling 12 before 12 = CALCULATE([Net], WINDOW(-23,REL, -12, REL, ADDCOLUMNS(ALLSELECTED('Date'[Month Year],'Date'[Month Year Sort] ),ORDERBY([Month Year Sort],asc)))

[Nikolainoergard]

Hello. Thank you for your reply, unfortunately it does not solve my problem @amitchandak