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] * DIV...
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.

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