Forum Discussion
DAX Issue
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”)
)
WHERE NOT (Hours_List = 23 AND Minutes_List > 44)
Any help would be wonderful
Thanks in advance
Matt
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_45See in the filter below that for 23 you only get untilm 23:45 for the rest of the hours you have until minute 59
3 Replies
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_45See in the filter below that for 23 you only get untilm 23:45 for the rest of the hours you have until minute 59
