Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
nickmart_bi
Frequent Visitor

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:

nickmart_bi_0-1691614524484.png

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.
1 ACCEPTED SOLUTION
nickmart_bi
Frequent Visitor

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.

 

nickmart_bi_0-1692121851642.png
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]
)

View solution in original post

3 REPLIES 3
nickmart_bi
Frequent Visitor

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.

 

nickmart_bi_0-1692121851642.png
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]
)
whitch
Resolver I
Resolver I

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. 

 

nickmart_bi_0-1692111267984.png

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?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.