topic Re: SWITCH conditions to populate values within a first date of month and Todays date range in DAX Commands and Tips

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

Deevo_ — Wed, 14 Jun 2023 01:33:39 GMT

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

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

Deevo_ — Wed, 14 Jun 2023 05:09:15 GMT

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

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

Deevo_ — Wed, 14 Jun 2023 06:08:58 GMT

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.