Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 flag, if it's Y then use the Rolling YTD, if it's N then use the Non Rolling YTD. The issue I am running into is that due to the gaps between my month year periods for certain IDs, thus I am returning Blanks in my switch column when I select/filter the specific month year in which the ID does not exist.

 

Question:

How do I pull in the correct YTD(measure) based on this flag that technically doesn't exist within a selected month year period? My "switch" column is named Total YTD.

 

Side Note: The Flag Column is consistent across dates.

 

Attempted Solution That Doesn't Work For MY Case:

  • Creating a DB View that contained empty rows for each ID and Month Year Period. The Dataset grows too large to be useful, even with premium resources.
  • Had troubles using a lookup table for my ID's and their appropriate flags, may have been an issue in the relationships or dax.

 

Current Attempt Sample:

Measures:

TOTAL MONTHLY SALES =
    IF(
        ISBLANK(
            SUM(TB_Sample_Data[MonthlySales Anon])
            )
        ,0
        ,SUM(TB_Sample_Data[MonthlySales Anon])
        )
 ---------------------------------------------------------------------------
_MonthSubInterval =

var currentdate=MAX(TB_Sample_Data[YEAR MONTH AS DATE])
var selecteddate = SELECTEDVALUE(DimDate[MonthYear])

return IF(ISBLANK(selecteddate),0,DATEDIFF(selecteddate, currentdate, MONTH))
 ---------------------------------------------------------------------------
NonRollYTD =
    CALCULATE(
        TOTALYTD([TOTAL MONTHLY SALES]
        ,DimDate[Date]))
-------------------------------------------------------------------------
RollingYTD = CALCULATE([TOTAL MONTHLY SALES], DATEADD(ALLSELECTED(DimDate[Date]), -[_MonthSubInterval], MONTH))
---------------------------------------------------------------------------
TOTAL YTD =
VAR FLAG = SELECTEDVALUE( TB_Sample_Data[Rolling YTD Flag] )
RETURN
SWITCH(
    FLAG,
    "Y", [RollingYTD],
    "N", [NonRollYTD]
)
---------------------------------------------------------------
I appreciate any help.
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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]
    )

3 Replies

  • whitch's avatar
    whitch
    Resolver I
    3 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Testing those Var Flags does fill in the blanks, but the "test" count measures are defaulting to false on the "blank" rows. 

       

      YTD Test N Flag =
      VAR FLAG = SELECTEDVALUE( TB_Sample_Data[Rolling YTD Flag], "N" )
      RETURN
      SWITCH(
          FLAG,
          "Y", [RollingYTD],
          "N", [NonRollYTD]
      )
      -----------------------------------------
      YTD Test Y Flag =
      VAR FLAG = SELECTEDVALUE( TB_Sample_Data[Rolling YTD Flag], "Y" )
      RETURN
      SWITCH(
          FLAG,
          "Y", [RollingYTD],
          "N", [NonRollYTD]
      )
      ----------------------------------------------
      Y Count > 0 = CALCULATE(COUNTROWS('TB_Sample_Data'),TB_Sample_Data[Rolling YTD Flag] = "Y") > 0
      ---------------------------------------------------
      N Count > 0 = CALCULATE(COUNTROWS('TB_Sample_Data'),TB_Sample_Data[Rolling YTD Flag] = "N") > 0
      --
      Am I not understanding something here?
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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]
    )