Help needed: How to calculate LTM

[Anonymous]

Hello everyone, 

I am struggeling to display the LTM Revenue for each month in a table. 

Following DAX is not working, as it doesn't give me any values for months prior to -12. 

LTM_Calc2 =

VAR EndDate = MAX(Period[Date_complete])

VAR StartDate = EDATE(EndDate, -11)

VAR Result =

CALCULATE(

    Facts[Actual],

    DATESBETWEEN(Period[Date_complete], StartDate, EndDate)

)

RETURN

Does anyone have an Idea how to calculte Last twelve months via Dax?

Kind regards

[pratyashasamal]

Hi @Anonymous ,
You can calculate last month in these ways :-
For example :-

Last 12 Months V1 =
CALCULATE ( 
      [Total Sales],
      DATESINPERIOD ( 'Date'[Date],          -- returns period from date column
                      MAX ( 'Date'[Date] ),  -- starting from MAX date
                      -12,                   -- shifting it back 12 intervals
                      MONTH                  -- each interval being a month
      )
)

 For example 2 :-

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Last 12 months V2 = VAR EndDate =     MAX( 'Date'[Date] ) -- retrieves MAX Date   VAR StartDate =     EDATE( EndDate, -12 ) + 1 -- shifts EndDate to year beginning VAR Result =     CALCULATE(         [Total Sales],         -- retrieves the relevant date range         DATESBETWEEN(             'Date'[Date],             StartDate,             EndDate         )     ) RETURN     Result

 Please follow this link to see more ways :-
https://gorilla.bi/dax/5-ways-to-calculate-last-12-months-in-dax/
Thanks ,

Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
Pratyasha  Samal

[MAwwad]

To calculate the last 12 months revenue, you can use the following DAX expression:

LTM_Revenue = CALCULATE( SUM(Facts[Actual]), DATESYTD(Period[Date_complete]), SAMEPERIODLASTYEAR(Period[Date_complete]) )

This expression calculates the sum of the actual revenue for the current date (using the DATESYTD function), and the same period of the previous year (using the SAMEPERIODLASTYEAR function). The result will be the revenue for the last 12 months.

You can then create a table that shows the LTM revenue for each month, using the following formula:

LTM_Revenue_Table = SUMMARIZE( Period, Period[Date_complete], "LTM Revenue", [LTM_Revenue] )

This expression summarizes the data by the Date_complete column, and calculates the LTM Revenue using the formula defined in the LTM_Revenue measure. The result will be a table that shows the LTM revenue for each month.