Reading rows of virtual date table to assign values for each row virtually - Power BI Dax

[Ram2502]

Hi Friends,

I am creating a virtual date table with start date and end date, In this table I want to read the rows virtually and assign working hours as

Monday 6.00AM to 11.59PM,

Tuesday as 12.00AM to 3.00AM & 6.00PM to 11.59PM, 

Wednesday as 12.00AM to 3.00AM & 6.00PM to 11.59PM, 

Thursday as 12.00AM to 3.00AM & 6.00PM to 11.59PM, 

Friday as 12.00AM to 3.00AM

Rest of the hours as non-working hours.

Please help me in getting this thru dax... Power BI

[Ram2502]

I need to calculate date difference between two dates, after getting the date difference, I need to assign in the case of Monday - 17 hrs, Tuesday - 20 hrs, Wednesday - 20 hrs, Thursday - 20 hrs, Friday - 3 hrs. And these 17 hrs, 20 hrs, 20 hrs, 20 hrs, 3 hrs also I need to calculate if the date difference is in below mentioned timings of each day. 

Monday - 5am to 11.59pm

Tuesday - 12.00am to 3am and 5am to 11.59pm

Wednesday - 12.00am to 3am and 5am to 11.59pm

Thursday - 12.00am to 3am and 5am to 11.59pm

Friday - 12.00am to 3am

Saturday - Holiday

Sunday - Holiday

Hope you understood the scenario

In these timings machines are running, i need to calculate machine stoppages in between if any, I am having machine stoppage start time and end time...

Need to ignore the timings of stoppages if the time falls other than working hours (as per above list)

[v-xinruzhu-msft]

Hi @Ram2502 

Can you provide the expected output you want?

You can create a calendar data , then add the column e.g 
Virtual_table=

ADDCOLUMNS(CALENDAR(DATE(2023,1,1),DATE(2023,12,31)),"Year",YEAR([Date]),"Month",MONTH([Date]),"Day",WEEKDAY([Date],2))
You can refer to the following link.

All in One: Script to Create Calendar Table or Date Dimension using DAX in Power BI - RADACAD

Examples Of Virtual Tables & Their Importance To A... - Microsoft Fabric Community

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.