Help with DAX to find the date for a weekday for the current week of the year

[kky1]

Hello, I need help with the syntax for a DAX expression. I am attempting to build an employee schedule on a calendar using the MAQ calendar visual. My data only has a weekday name and a start time and end time. I have a date table also with every day from 2022-2024 which I created a today column in my fact table and linked to the date in the date table. 

I also tried creating a new table filtering from my date table with: Table = filter('DateTable', 'DateTable'[Week of Month] = weeknum(today())). It didn't throw an error but there's no data in it.

What I want is to dynamically determine the actual date of the day of the week for the current week. I've tried variations of functions (today(), utc now(), date(), weekday() and weeknum but I'm getting errors. I'm truly struggling. Any help would be appreciated. 

At the end of the day, I need to take data that is currently stored as:

ID	MondayStart time	MondayEndTime	TuesdayStartTime	TuesdayEndTime	WednesdayStartTime	WednesdayEndTime	ThursdayStartTime	ThursdayEndTime	FridayStartTime	FridayEndTime
1	7:00	4:00	8:00	5:00	6:00	3:00	7:30	16:30	10:00	20:00
2					5:30	4:00
3					8:00	14:00	6:30	17:00
4	8:30	4:30					8:00	14:00	6:30	17:00

and I need to convert it into a new table in Power BI with the dates dynamically updating based on the current week. 

So for week # 33 of this year (2022) it would be:

(which I've gotten to in this format with Unpivot in power query)

ID	Date	Start time	End Time
1	8/8/2022	7:00	4:00
2	8/8/2022
3	8/8/2022
4	8/8/2022	8:30	4:30
1	8/9/2022	8:00	5:00
2	8/9/2022
3	8/9/2022
4	8/9/2022
1	8/10/2022	6:00	3:00
2	8/10/2022	5:30	4:00
3	8/10/2022	8:00	14:00
4	8/10/2022
1	8/11/2022	7:30	16:30
2	8/11/2022
3	8/11/2022	6:30	17:00
4	8/11/2022	8:00	14:00
1	8/12/2022	10:00	20:00
2	8/12/2022
3	8/12/2022
4	8/12/2022	6:30	17:00

However, I'm running into an issue with the MAQ Calendar visual in that the null values for the hours on certain dates is populating in the calendar as "all day events" when I want them not to show on the calendar. Does anyone know of a workaround for that?

Thank you in advance for any help!

[kky1 · Accepted solution]

I'm sure there is likely to be a better way to work this out, but I was able to solve my issue of dynamically having M-F dates on a calendar with the following steps.

1. create today column
Today = Today()

2. create weeknum column for 1st Monday of the week:
Weeknum = WEEKNUM('ID'[Today],2)

3. create First Monday column:
First Monday =
var _mindate=CALCULATE(MIN('Table'[today]),FILTER('Table','Table'[Weeknum]=EARLIER('Table'[Weeknum])))
var _weekday=WEEKDAY(_mindate,2)
Return
IF(_weekday<>1,DATE(YEAR(_mindate)-1,12,31-(_weekday-1)+1),_mindate)

3. create current weekday based on weekday names:
CurrentWkday =
SWITCH(
TRUE(),
Table[DayName]="Monday", Table[First Monday]+0,
Table[DayName]="Tuesday", Table[First Monday]+1,
Table[DayName]="Wednesday", Table[First Monday]+2,
Table[DayName]="Thursday", Table[First Monday]+3,
Table[DayName]="Friday", Table[First Monday]+4,
BLANK()
)

4. Create Date Time:
CurrentDtTime = Table[CurrentWkday]&" "&Table[EndTimes]

Last step, I just did a datetime formula to concatenate the dates I'd generated with the time slots in my table to create a datetime for the calendar.

Side note: I'm still getting "all day" on the MAQ Calendar visual even though the values are null. If anyone has a solution to that, I'd be most grateful! 

[kky1]

Hi @littlemojopuppy - thank you so much for the reply. I couldn't attach a file to my post so I uploaded it to a public repository in GitHub - https://github.com/kky1/SampleFiles 

Steps so far:

I unpivoted the start times and end times separately into two tables. Then added a column to get the week of year number and the day name of the week. 

I then created a date table and a time table using scripts at this web site: https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query and https://radacad.com/script-for-creating-time-table-in-power-bi-with-hours-minutes-and-seconds-bucket...

I thought I'd try linking the end time table and start time table each to a date and time table from the script above on week of year number and day name. I'm sure there's a more simplistic way to do this though...

As far as the MAQ calendar custom visual, I'm not sure why it is showing null or empty date values as "all day" on the calendar but I'm looking into that separately in case it's a bug. 

Thank you so much for your insight!

[littlemojopuppy]

Hi @kky1 can you provide some sample data to play with?