Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Help needed: How to calculate LTM

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

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • MAwwad's avatar
    MAwwad
    Solution Sage
    3 years ago

    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.

  • pratyashasamal's avatar
    pratyashasamal
    Memorable Member
    3 years ago

    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