DAX Issue

[Matt_dev]

Hi,

I have the following DAX (credit https://addendanalytics.com/blog/calculate-working-hours-in-power-bi) which creates a time from hours and minutes between 06:00 and 23:59 and I want to make a small change:

var Hours_List = SELECTCOLUMNS(GENERATESERIES((6), (23)), “Hour”, [Value])

var Minutes_List = SELECTCOLUMNS(GENERATESERIES((0), (59)), “Minute”, [Value])

var two_years_ago_start = DATE(YEAR(NOW())-2,1,1) //DATE(2018,1,1)

var one_year_later_end = DATE(YEAR(NOW())+1,12,31)

var Dates_List = CALENDAR(two_years_ago_start,one_year_later_end)

var HoursandMinutes = ADDCOLUMNS(

CROSSJOIN(Hours_List, Minutes_List),

“Time”, TIME([Hour], [Minute], 00),

“Validity”, IF([Hour]<6 || [Hour]>23,”Non working”,”Working”)

)

I would like the list to stop at 23:45 but only for hour 23. I want the window to be 06:00 to 23:45 but still count the last 15 minutes in all other hours. My instinct is to use an IF on the ADDCOLUMNS function used in setting the variable HoursandMinutes but I'm not sure exactly what it would look like as I am used to managing these changes in SQL where I would say:

WHERE NOT (Hours_List = 23 AND Minutes_List > 44) 

Any help would be wonderful

Thanks in advance

Matt 

[MFelix · Accepted solution]

Hi  , 

Just add a filter function to the HoursandMinutes something similar to this:

Hour minutes tables = var Hours_List = SELECTCOLUMNS(GENERATESERIES((6), (23)), "Hour", [Value])

var Minutes_List =  SELECTCOLUMNS(GENERATESERIES((0), (59)), "Minute", [Value])

var two_years_ago_start = DATE(YEAR(NOW())-2,1,1) //DATE(2018,1,1)

var one_year_later_end = DATE(YEAR(NOW())+1,12,31)

var Dates_List = CALENDAR(two_years_ago_start,one_year_later_end)

var HoursandMinutes = ADDCOLUMNS(

CROSSJOIN(Hours_List, Minutes_List),

"Time", TIME([Hour], [Minute], 00),

"Validity", IF([Hour]<6 || [Hour]>23,"Non working","Working")

)

VAR HourMinutes_23_45 = FILTER( HoursandMinutes, [Time] <= TIME(23,45,00))
RETURN
HourMinutes_23_45

See in the filter below that for 23 you only get untilm 23:45 for the rest of the hours you have until minute 59

@Matt_dev

[Matt_dev]

Thank you Miguel, that has worked perfectly 

[MFelix]

Hi @Matt_dev ,

Don't forget to accept the correct answer so it can help out others.