SWITCH conditions to populate values within a first date of month and Todays date range

[Deevo_]

Hi All,

I would like some assistance in modifying a dax switch statement which will only populate values if within a certain date range.

What the switch measure is current doing:

• I currently have a table which is "page filtered" to display the date for everyday for the month of June.
• The SWITCH measure is populating values for every day of the month that is displayed in the table.

What I need the switch measure to do:

• I need the switch measure to only populate values from the first date of the month only up until todays date.

Date range I want the values populated for:

• Start date = first date of the current month
• End Date = Todays date (this will be the local system date)

What I current have:

• 4 tables:
  • Staff details
    • Fields = LoginID, Staff Full Name
      • Link Key = LoginID
  • Timesheet details
    • Fields = LoginID, Staff Full Name, Date of Timesheet, Datekey, Timesheet Hours
      • Link Key 1= LoginID
      • Link Key 2 = Datekey
  • Leave details (This data is stored in a day by day format. So if one person takes 7 days of leave, then there will be 7 entries)
    • Fields = LoginID, Staff Full Name, Date of each leave day, Datekey, Leave Events, Leave reason
      • Link Key 1= LoginID
      • Link Key 2 = Datekey
  • Dim_Date
    • Datekey, Date....and everything that a normal date table would contain.
      • Link Key = Datekey
• 3 measures:
  • Total Leave Days = SUM([Leave_Events])
  • Total Timesheet hours = SUM(Timesheet_Hours)
  • SWITCH Statement:

Check for Leave Days and Timesheet = 

SWITCH(

    TRUE(),

    [Total Leave Days] > 0 && [Total Timesheet hours] > 0,"N", 

    [Total Leave Days] > 0 && [Total Timesheet hours] <= 0,"Y", 

    [Total Leave Days] <= 0 && [Total Timesheet hours] > 0,"NA", 

    [Total Leave Days] <= 0 && [Total Timesheet hours] <= 0,"Unknown",    

    BLANK()

     )

Can you please assist to modify the current switch statement to include the date range condition?

Many Thanks

[Deevo_]

I have tried this updated measure but it does not appear to be restricting the values to the specified date range. I still have values in date column that are GREATER than todays date

Check for Leave Days and Timesheet = 

var startdate = DATE(2023,06,01)

var enddate = TODAY()

var daterange = DATESBETWEEN(Dim_Date[Date], startdate, enddate)

var check =

CALCULATE(

SWITCH(

    TRUE(),

    [Total Leave Days] > 0 && [Total Timesheet hours] > 0 ,"N",     

    [Total Leave Days] > 0 && [Total Timesheet hours] <= 0,"Y",    

    [Total Leave Days] <= 0 && [Total Timesheet hours] > 0,"NA",    

    [Total Leave Days] <= 0 && [Total Timesheet hours] <= 0, "Unknown",   

    BLANK()

     ),

     daterange)

RETURN check

Any ideas?

Thanks

[Deevo_ · Accepted solution]

I have solved this by using this post --> Solved: Return/generate value based on date range - Microsoft Fabric Community

This is my final updated measure:

Check for Leave Days and Timesheet = 

var startdate = DATE(2023,06,01)

var enddate = TODAY()

var check =

SWITCH(

    TRUE(),

    [Total Leave Days] > 0 && [Total Timesheet hours] > 0 ,"N",     

    [Total Leave Days] > 0 && [Total Timesheet hours] <= 0,"Y",    

    [Total Leave Days] <= 0 && [Total Timesheet hours] > 0,"NA",    

    [Total Leave Days] <= 0 && [Total Timesheet hours] <= 0, "Unknown",   

    BLANK()

     )

RETURN IF(

    MIN(Dim_Date[Date]) >= startdate

        && MAX (Dim_Date[Date]) <= enddate,

        check,

        BLANK()

)

Thanks to anyone who has stopped by to read this post.