Forum Discussion
Using flags for switch statements with gaps in dates
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])
I suspect the problem is that SELECTEDVALUE is not returning a value for you. Experiment with replacing:
VAR FLAG = SELECTEDVALUE( TB_Sample_Data[Rolling YTD Flag] )
with
VAR FLAG = SELECTEDVALUE( TB_Sample_Data[Rolling YTD Flag] , "Y")
or
VAR FLAG = SELECTEDVALUE( TB_Sample_Data[Rolling YTD Flag] , "N")
These give SELECTEDVALUE a default value when it can't work it out. This may not seem immediately useful as you don't want it to default to Y or N, but will at least tell us where the issue is. If, in your visual, you still have blanks then it must be something else.
If this is the problem, then we need to replace SELECTEDVALUE with something else. Try creating a measure like this:
test = CALCULATE(COUNTROWS('TB_Sample_Data'),TB_Sample_Data[Rolling YTD Flag] = "Y") > 0
This should return True/False instead of Y/N.
If this works, then incorporate this into your [TOTAL YTD] measure, replacing the VAR FLAG definition you currently have.
Testing those Var Flags does fill in the blanks, but the "test" count measures are defaulting to false on the "blank" rows.