Forum Discussion

Nikolainoergard's avatar
Nikolainoergard
Frequent Visitor
2 years ago
Solved

LTM measure issue

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    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))

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.