dynamic time
1 Topic
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.Solved567Views0likes1Comment