LTM measure issue

[Nikolainoergard]

Hi
I have a volume effect measure that I'm having issues with when applying an LTM calculation. I believe it's related to a context issue.
This itself works fine:

Volume effect = [Sales PY] * DIVIDE([Trench Volume], [Trench Volume PY]) - [Sales PY]

My LTM measure, which works fine for other measures like Sales etc:

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

PY's:

VAR _Y = MAX('pbi DimDate'[Calendar_YearInt])-1
RETURN
CALCULATE([Measure], 'pbi DimDate'[Calendar_YearInt]=_Y)

I can't use any time intelligence formulas due to accounting periods.
Any tips?
Thanks

[Anonymous · Accepted solution]

Hi, @Nikolainoergard 

Based on your information, I create a sample table:

Then create a calculated column and try the following DAX expression:

Volume effect = [Sales_PY] * DIVIDE([Trench_Volume], [Trench_Volume_PY]) - [Sales_PY]

Create a measure:

Volume Effect LTM = 
VAR _MaxPeriod = MAX('Table'[Calendar_ConsecutiveFiscalPeriod])
VAR _MinPeriod = _MaxPeriod - 12
VAR _Result =
    CALCULATE(
        SUM('Table'[Volume effect]), 
        ALL('Table'),
            'Table'[Calendar_ConsecutiveFiscalPeriod] <= _MaxPeriod &&
            'Table'[Calendar_ConsecutiveFiscalPeriod] > _MinPeriod
    )
RETURN
    _Result

Here is my preview:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[amitchandak]

@Nikolainoergard , for Last 12 monthly or rolling 12 month you can try like

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

Using window functions

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

Last year

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))