topic Change Start of the day in DAX Commands and Tips

Change Start of the day

ItoDiaz — Fri, 22 Apr 2022 14:23:05 GMT

Hello all,

I have found a calendar with severals columns I need as indexDay, indexWeek... It works for a part of my sources, but I have some sources how don't start their day at 00:00 but at 05:00 AM.

I have tried several things to change my start of the day but nothing change.

I need that my date (Day) start at 05:00 AM and end at 4:59 AM of the Day+1 and in concecuency my others periods follow this start and end. Ex : last IsoWeek have started on 04/11/22 at 5:00 AM and it end was on 04/18/22 at 4:59 AM

Could you help me please?

Date sort = GENERATE ( CALENDAR( DATE( YEAR( TODAY() ) - 3, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()), VAR startOfWeek = 2 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday VAR currentDay = [Date] VAR days = DAY( currentDay ) VAR weeks = WEEKNUM(( currentDay ),21) VAR months = MONTH ( currentDay ) VAR years = YEAR ( currentDay ) VAR nowYear = YEAR( TODAY() ) VAR nowMonth = MONTH( TODAY() ) VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1 VAR todayNum = WEEKDAY( TODAY(),2 ) VAR weekIndex = ROUNDDOWN((dayIndex + todayNum - startOfWeek - 5)/7,0) //VAR weekinMonthIndex = (1 + WEEKNUM( currentDay ) - WEEKNUM( STARTOFMONTH (TODAY()),21)) RETURN ROW ( "day", days, "month", FORMAT(months,"0#"), "week", FORMAT(weeks,"0#"), "year", years, "day index", dayIndex, "week index", weekIndex, "month index", INT( (years - nowYear ) * 12 + months - nowMonth ), "year index", INT( years - nowYear ) //"WeekinMonthIndex", weekinMonthIndex ) )

Re: Change Start of the day

amitchandak — Fri, 22 Apr 2022 16:43:38 GMT

ItoDiaz , You have create new date column in your tables and then join that with date table

if(Timevalue([DateTime]) <time(5,0,0) , datevalue([Datetime]) -1, datevalue([Datetime]))

Re: Change Start of the day

ItoDiaz — Mon, 25 Apr 2022 08:37:34 GMT

amitchandak

Thanks a lot for your answer. Sorry I'm new using Dax and Power BI. I have done your suggestion, and of cours it works in my SourceTable,

but I don't understand how I can use it to do my filters as I do everything with my DateSortTable that don't change.

I think you know, in my DateSortTable my VAR CurrentDay = [Date] is regarding the date column created by CALENDAR fonction and is this CurrentDay or the Start Hour in my CALENDAR function, I would like to change but the system don't propose me other options. I have tried to add 5/24 to my CALENDAR but it doesn't works.

Should I create all my DateSortTable into My SourceTable?

Re: Change Start of the day

v-kkf-msft — Tue, 26 Apr 2022 05:19:50 GMT

Hi ItoDiaz ,

Please add the column: "NewDate", [Date] + TIME(5,0,0)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Change Start of the day

ItoDiaz — Thu, 28 Apr 2022 08:29:04 GMT

Hello v-kkf-msft,

Your suggestion have helped me to solve my problem. I have created another VAR using your code "NewDate", [Date] + TIME(5,0,0) and I put this VAR as result of my CurrentDay and it works 🙂

Thanks a lot