Power BI Custom Date and Time Query

[vinayk]

I have a Date measure that calculates the total line production in a day and they range from 12 midnight to 11.59pm. Now I need to calculate the same between 6am to 5.59 am next day. I have the below power query already written for Date column

Date=
ADDCOLUMNS(
CALENDAR (DATE (2015,1,1), DATE (2025, 12, 31) ),
"DateKey", FORMAT ( [Date], "YYYYMMDD"),
"YearKey", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date],"MvMM"),
"YearMonthShort", FORMAT ( [Date], "mmm M" ),
"MonthNameShort", FORMAT ( [Date],
"MonthNameLong", FORMAT ( [Date],
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfweek", FORMAT ( [Date],"dddd" )
"DayOfheekShort", FORMAT ( [Datel," ddd"),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "M" ) & "/Q" & FORMAT ( [Date], "Q" )
 )

[amitchandak · Accepted solution]

@vinayk , No need to change the date calendar. Change the date in fact 

New column =

var _time = timevalue([Datetime])

var _date = datevalue([Datetime])

return

if(_time<time(6,0,0) , _date -1, _date)

Join this new date date column with date table

[vinayk]

Thanks a lot @amitchandak

It worked 🙂