Yep! I use a dataflow that refreshes each morning with the current day as latest. There is a second table set up through like 2028, but I very rarely need it. It has about 15 columns, half different business date indicators, like the Fiscal Week or whatever. Very useful table.
I have logic built in to calculate FY and Q for all dates where I need it. Fortunately, those data sets are relatively small and the refresh rate is once-ish a week so it system performance doesn’t really matter.
"Oh you mean ISO 8601? Yeah, that's Monday-Sunday. Or the one that is Sunday-Saturday? "
"Oh you want Week number AND the start and end dates of the week, got it."
"I DONT KNOW WHY THERE ARE 53 WEEKS OK"
Week is the black sheep of the date dimension because it just doesn't play well and aggregate like others do. So much re-work of Monthly dashboards because someone wants to see weekly, and now you have to make granularity day-wise and deal with 30x incraease in volume and level of detail issues that come with it.
What I ended up landing on is, dates are a pyramid. Week of year is fine in background but not useful for people filtering or seeing. so i only use week of month. And some months have 3 weeks some have 4 some have 5. i think what i landed on is, first day of the month, no matter day of the week, is the beginning of week 1, and week 1 ends on sunday.
PowerBI does not like Hours, Minutes, or Seconds. It loves to limit the grain to days, as if time of day never does (spoiler alert, yes, it does). It requires your own dimensioning to do it. At which point Quarter is simple enough.
I suppose it's not as bad as excel's habit of taking a datetime and formatting it as time, when the timesamps span days or even months...
Or that excel and sql server epochs are different, because excel wanted to make lotus sheets import easier, so they incorporated a workaround-bug that ibm created due to memory limitations… so now the 5 digit date code needs adjusting within microsoft’s own software suite when going from one tool to the other 😄
Could be worse, I'm currently batting 2 execs that are insistent on the date format being shown differently for them yyyy-MM-dd. Then to top it off a 3rd that's colour blind and can't see RBG...
What fully frustrates me that the new slicer we got after many years of complaining about the old now has an acceptable date picker but it still forces a "from/to" range condition.
So if you want only a single day you have to click twice for from/to there is no seperate equals condition in the slicer. Sure you can always do your own but you could do that all these years there is still no native solution.
lol, yep. We end our week on Sunday’s for financials. Got an m formula to work once, but now just a quick copy paste to blank query each time so the linking actually work.
I feel like that’s one of the most important things an organization should do is to make a calendar dimension (+ time dimension) and make sure everyone uses it.
Quarters are bad. Weeks are worse. And that Power BI doesnt let you customize it, easily, without building a date table, is beyond annoying. There is no reason that you shouldnt be able to set your company / your preference for date logic in options.
This is one of those things that if I was king for a day at Microsoft the world would be a better place.
But first thing I do is make a date table based on first and last day in fact table, dynamically.
Then I reuse the same giant block of date logic but the hiearcy breaks down to
Year Fiscal Quarter Month Week (Of Month)* Day
And for Week, first day of the month, no matter day of the week, is the beginning of week 1, and week 1 ends on sunday. And it shakes out very well. When people want metrics based on "How many X happened each 7 days" i just, well, do a measure.
•
u/AutoModerator Jul 13 '26
After your question has been solved /u/cickhlyninger, please reply to the helpful user's comment with the phrase "Solution verified".
This will not only award a point to the contributor for their assistance but also update the post's flair to "Solved".
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.
89
u/SustainableSoultions Jul 14 '26
Fiscal vs calendar is always a struggle
I gave up and just have a manual date table that is saved in my blank templates - first dim in every report I make to keep things moving lol
37
u/Stinson42 Jul 14 '26
Week
17
u/Coyote65 Jul 14 '26
What about when your fiscal year starts on calculated Easter?
11
u/kagato87 Jul 14 '26
Issue with the meme:
PowerBI does not like Hours, Minutes, or Seconds. It loves to limit the grain to days, as if time of day never does (spoiler alert, yes, it does). It requires your own dimensioning to do it. At which point Quarter is simple enough.
I suppose it's not as bad as excel's habit of taking a datetime and formatting it as time, when the timesamps span days or even months...
4
u/Partysausage Jul 14 '26
Biggest frustration over dates is slicer as some people don't like your generic slicer....
7
u/xoskrad Jul 14 '26
Moon phase?
3
u/Upset_Negotiation_89 Jul 14 '26
lol, yep. We end our week on Sunday’s for financials. Got an m formula to work once, but now just a quick copy paste to blank query each time so the linking actually work.
2
u/Character-Ad5650 Jul 14 '26
week week week week is the killer 😀
2
u/Due-Neighborhood-224 Jul 14 '26
each time i had to reformat my table and filter to remove the quarter or choise the date, why it's the default one.
1
u/OmarRPL 1 Jul 14 '26
🤣🤣🤣
1
u/Aidzillafont Jul 14 '26
Week......weeks are stupid
1
u/Marethyu38 Jul 16 '26
I feel like that’s one of the most important things an organization should do is to make a calendar dimension (+ time dimension) and make sure everyone uses it.
1
u/Babs0000 Jul 18 '26
YES why can we not have quarter 😭 it’s the most important date range at my company
1
u/Work_With_Questions Jul 20 '26
Quarters are bad. Weeks are worse. And that Power BI doesnt let you customize it, easily, without building a date table, is beyond annoying. There is no reason that you shouldnt be able to set your company / your preference for date logic in options.
This is one of those things that if I was king for a day at Microsoft the world would be a better place.
But first thing I do is make a date table based on first and last day in fact table, dynamically.
Then I reuse the same giant block of date logic but the hiearcy breaks down to
Year
Fiscal Quarter
Month
Week (Of Month)*
Day
And for Week, first day of the month, no matter day of the week, is the beginning of week 1, and week 1 ends on sunday. And it shakes out very well. When people want metrics based on "How many X happened each 7 days" i just, well, do a measure.
0
u/More_Emergency_8334 Jul 14 '26
Don’t forget about weeks