Forum Discussion

StuartPQY's avatar
StuartPQY
Frequent Visitor
1 year ago
Solved

Dynamic Time comparison across multiple years

Hello

 

I am hoping someone can help with this.  I have searched for several days and what ive seen is not quite right.

 

Scenario, I am creating an "Archive" set of data for expenses that will eventually span 2023 to 2031.  My data as now is only 2024 and 2025.

 

I wish to create a a view that will compare YTD or MTD across the years.  The code snippet below works perfectly for the current year only.

 

Value_CurrentPeriod = VAR PeriodSelector =
    SELECTEDVALUE(SEL_PERIOD[Period])

VAR LastDaySelected =
    MAX('DIM_Date'[Date])
VAR MonthSelectedDate =
    MONTH(LastDaySelected)
VAR YearSelectedDate =
    YEAR(LastDaySelected)
VAR QuarterSelectedDate =
    QUARTER(LastDaySelected)

VAR FilterDatesLTM =
        DATESINPERIOD('DIM_Date'[Date],LastDaySelected, -12, MONTH)

VAR MonthStartFinancialYear = 13
VAR YearFinancialYear =
    IF(MonthStartFinancialYear>MonthSelectedDate,YearSelectedDate-1, YearSelectedDate)
VAR FinancialStartDate =
    DATE(YearFinancialYear,MonthStartFinancialYear,1)
VAR FilterDatesYTD =
    DATESBETWEEN('DIM_Date'[Date],FinancialStartDate,LastDaySelected)

VAR Units =
    SELECTEDVALUE(SEL_UNITS[UnitsDivider])

VAR Result = SWITCH(PeriodSelector,
                        "MTD", CALCULATE(SUM('ArchiveData'[Value])),
                        "LTM", CALCULATE(SUM('ArchiveData'[Value]),FilterDatesLTM),
                        "YTD", CALCULATE(SUM('ArchiveData'[Value]),FilterDatesYTD))

VAR ResultUnits =
    Divide (Result,Units)
Return ResultUnits
 
Where the SEL tables that are mentioned are additional slicers.
 
I may be over complicating this to start as the code above is my start position.  (I use this on the in current year reports for Actual vs Budget)
 
My objective is similar code to the above that will dynamically calculate across the years as new years are added, i.e. I dont have to create a new measure each January.
 
I have seen commands such as SAMEPRIODLASTYEAR but this not good as I have several years.
 
I am hoping someone can provide some guidance on the sort of a solution I need.
DAX
Dynamic Time
  • DataNinja777's avatar
    DataNinja777
    1 year ago

    Hi StuartPQY ,

     

    To achieve dynamic MTD, YTD, and LTM comparisons across multiple years without rewriting the measure each January, you can modify your current DAX logic to be context-aware. Rather than focusing on a single selected year, the new measure should calculate values based on the row context of the visual. This approach allows comparisons across all years in your dataset without relying on hardcoded logic or limited functions like SAMEPERIODLASTYEAR.

    Here’s a revised version of your measure that works dynamically across years. It respects slicer selections such as SEL_PERIOD and SEL_UNITS, and adapts to visual context, enabling you to drop it into a matrix with Year and Month and see side-by-side values for each year.

    Value_Period_By_Year = 
VAR PeriodSelector = SELECTEDVALUE(SEL_PERIOD[Period])
VAR Units = SELECTEDVALUE(SEL_UNITS[UnitsDivider], 1)

VAR LastDateVisible = MAX('DIM_Date'[Date])
VAR ContextYear = YEAR(LastDateVisible)
VAR ContextMonth = MONTH(LastDateVisible)

VAR MonthStartFinancialYear = 13
VAR YearFinancialYear = IF(ContextMonth < MonthStartFinancialYear, ContextYear - 1, ContextYear)
VAR FinancialStartDate = DATE(YearFinancialYear, MonthStartFinancialYear, 1)

VAR MTD_Filter = DATESMTD('DIM_Date'[Date])
VAR LTM_Filter = DATESINPERIOD('DIM_Date'[Date], LastDateVisible, -12, MONTH)
VAR YTD_Filter = DATESBETWEEN('DIM_Date'[Date], FinancialStartDate, LastDateVisible)

VAR Result = 
    SWITCH(
        TRUE(),
        PeriodSelector = "MTD", CALCULATE(SUM('ArchiveData'[Value]), MTD_Filter),
        PeriodSelector = "YTD", CALCULATE(SUM('ArchiveData'[Value]), YTD_Filter),
        PeriodSelector = "LTM", CALCULATE(SUM('ArchiveData'[Value]), LTM_Filter)
    )

RETURN DIVIDE(Result, Units)

    This version uses MAX('DIM_Date'[Date]) to pick up the latest visible date in the visual context and adjusts calculations accordingly. Since it doesn't filter to a specific year, it can be used in visuals that include multiple years without additional modification. Simply add your date hierarchy (e.g., Year and Month) to a matrix or line chart, and this measure will compute MTD, YTD, and LTM values for each year dynamically.

     

    Best regards,

1 Reply

  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    Hi StuartPQY ,

     

    To achieve dynamic MTD, YTD, and LTM comparisons across multiple years without rewriting the measure each January, you can modify your current DAX logic to be context-aware. Rather than focusing on a single selected year, the new measure should calculate values based on the row context of the visual. This approach allows comparisons across all years in your dataset without relying on hardcoded logic or limited functions like SAMEPERIODLASTYEAR.

    Here’s a revised version of your measure that works dynamically across years. It respects slicer selections such as SEL_PERIOD and SEL_UNITS, and adapts to visual context, enabling you to drop it into a matrix with Year and Month and see side-by-side values for each year.

    Value_Period_By_Year = 
VAR PeriodSelector = SELECTEDVALUE(SEL_PERIOD[Period])
VAR Units = SELECTEDVALUE(SEL_UNITS[UnitsDivider], 1)

VAR LastDateVisible = MAX('DIM_Date'[Date])
VAR ContextYear = YEAR(LastDateVisible)
VAR ContextMonth = MONTH(LastDateVisible)

VAR MonthStartFinancialYear = 13
VAR YearFinancialYear = IF(ContextMonth < MonthStartFinancialYear, ContextYear - 1, ContextYear)
VAR FinancialStartDate = DATE(YearFinancialYear, MonthStartFinancialYear, 1)

VAR MTD_Filter = DATESMTD('DIM_Date'[Date])
VAR LTM_Filter = DATESINPERIOD('DIM_Date'[Date], LastDateVisible, -12, MONTH)
VAR YTD_Filter = DATESBETWEEN('DIM_Date'[Date], FinancialStartDate, LastDateVisible)

VAR Result = 
    SWITCH(
        TRUE(),
        PeriodSelector = "MTD", CALCULATE(SUM('ArchiveData'[Value]), MTD_Filter),
        PeriodSelector = "YTD", CALCULATE(SUM('ArchiveData'[Value]), YTD_Filter),
        PeriodSelector = "LTM", CALCULATE(SUM('ArchiveData'[Value]), LTM_Filter)
    )

RETURN DIVIDE(Result, Units)

    This version uses MAX('DIM_Date'[Date]) to pick up the latest visible date in the visual context and adjusts calculations accordingly. Since it doesn't filter to a specific year, it can be used in visuals that include multiple years without additional modification. Simply add your date hierarchy (e.g., Year and Month) to a matrix or line chart, and this measure will compute MTD, YTD, and LTM values for each year dynamically.

     

    Best regards,