Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
)
)
Solved! Go to Solution.
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)
)
)
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.
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)
)
)
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 🙂
Thanks a lot
@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]))
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?
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
8 |
User | Count |
---|---|
31 | |
27 | |
15 | |
13 | |
10 |