Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ItoDiaz
Helper I
Helper I

Change Start of the day

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
)
)

 

1 ACCEPTED SOLUTION
v-kkf-msft
Community Support
Community Support

Hi @ItoDiaz ,

 

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

 

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
"NewDate", [Date] + TIME(5,0,0)
)
)

vkkfmsft_0-1650949908766.png

 

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.

View solution in original post

4 REPLIES 4
v-kkf-msft
Community Support
Community Support

Hi @ItoDiaz ,

 

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

 

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
"NewDate", [Date] + TIME(5,0,0)
)
)

vkkfmsft_0-1650949908766.png

 

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.

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 🙂 

 

ItoDiaz_0-1651134519543.png

 

Thanks a lot 

amitchandak
Super User
Super User

@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]))

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

ItoDiaz_3-1650875416889.png

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? 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors