Forum Discussion
Using flags for switch statements with gaps in dates
Context: I am attempting to create a switch YTD column that selects either a Rolling YTD (From the first available amount onward) or a Non Rolling YTD (Regular YTD). The switch is dependent on a fla...
Created column yearmonth as a date, using the first of the month for the day. Then created Last Remark measure which provides you a Rolling YTD Flag that is not blank (on the backend; Original Rolling YTD Flag has conditional formatting on to highlight blank values). The Total YTD measure now references the last remark measure.
Latest remark = calculate(lastnonblankvalue(TB_Sample_Data[YEAR MONTH AS DATE],Max(TB_Sample_Data[Rolling YTD Flag])), ALLEXCEPT(DimDate, DimDate[Date]))-------------------------------------------------------------TOTAL YTD =SWITCH(TRUE(),[Latest remark]="Y", [RollingYTD],[Latest remark]="N", [NonRollYTD])
Created column yearmonth as a date, using the first of the month for the day. Then created Last Remark measure which provides you a Rolling YTD Flag that is not blank (on the backend; Original Rolling YTD Flag has conditional formatting on to highlight blank values). The Total YTD measure now references the last remark measure.
Latest remark = calculate(lastnonblankvalue(TB_Sample_Data[YEAR MONTH AS DATE],Max(TB_Sample_Data[Rolling YTD Flag])), ALLEXCEPT(DimDate, DimDate[Date]))
-------------------------------------------------------------
TOTAL YTD =
SWITCH(
TRUE(),
[Latest remark]="Y", [RollingYTD],
[Latest remark]="N", [NonRollYTD]
)