Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power BI Custom Date and Time Query

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's avatar
    amitchandak
    4 years ago

    Anonymous , 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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , 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