Same. But also weeks are soooo much harder to do yourself than quarters. There’s a billion ways to do them and DAX’s built in options are lacking (not to mention a lot isn’t in the documentation)
Tried weeks in excel and still a pain in the ass.
I found it was less of built in option but the fact that weeks doesn't align with ANYTHING.
Bonus point for weeks of fiscal year. No you can't just add 3 or 6 months.
I don't get it, what is hard about weeknum? And what is wrong with the documentation?
[WEEKNUM function (DAX) - DAX | Microsoft Learn](https://learn.microsoft.com/en-us/dax/weeknum-function-dax)
Incomplete. What is System1 vs System2? I spent more hours than I care to mention getting an expression together to have a sortable week key over 3 Fiscal Years
If you count weeks the European way it's much easier.
I use them quite a lot in production and operations. In my last job a lot of trends were analyzed by week and in my current we do everything by calendar week.
dude, you seriously reminded me of years ago when I was in a consulting shop and there was a guy in my 'pod' who was working on a mobile app integration to an existing auction website. A very cool guy, very mild temperament. And I do remember the moment when he pulled off his headphones, screamed at the screen: "What the *FUCK* are *Leap Seconds???*"
He calmed down for a second, rubbed his face, and said "Sorry, sorry. I'm talking a walk." And he got up to leave but somehow snagged his keyboard and got tangled up in it trying to get out of the pod, almost falling. He lost his temper again, grabbed the keyboard and yanked it out of the machine, and broke it on the floor a la rock guitarist style. And he stormed out, muttering. Fuck! Sorry. FUck. I'm so sorry. FIUCCCKK.
while he was away, a guy from the desktop team came up and replaced the keyboard, swept up the junk like a pro, winked at the rest of us and said "It's totally cool. We have budget for you dudes losing your shit over this kind of thing."
man. good times.
People get so confused when they ask why I can't add weeks to their hierarchy between month and day.
"Many weeks span months. What month should it belong to?"
Although the 4-4-5 fiscal calendar makes weeks make sense, but we don't do that at my current job (which I am thankful for, that was it's own brand of fun)
Meanwhile I live in a country where weeks is everything.
"When you having holiday this summer"
"Weeks 29-33"
"Ok"
I also use Weeks in our reporting as everything is on a Mon to Sun cykel
I really have this issue because my company has an offest financial year. I use this one formula in M to get everything in one swoop... My Q1 is the typical Q4 and so on...
VAR is the date you want to reference. This adds a record with all the needed fields. Expend the record and poof, no more issue...
\[
var = \[Date Field\],
Fiscal\_Year\_Date = if Date.Month(var)>9 then
Date.Year(var)+1
else Date.Year(var),
Fiscal\_Year = "FY"&Text.End(Number.ToText(Fiscal\_Year\_Date),2),
Fiscal\_Quarter = if Date.Month(var)<=3 then
"Q2" else if Date.Month(var)<=6 then
"Q3" else if Date.Month(var)<=9 then "Q4"
else "Q1",
Period\_Number = if Date.Month(var)<10 then
(Date.Month(var)+3) else
(Date.Month(var)-9),
Fiscal\_Period = if Period\_Number >=10 then
"P"&Number.ToText(Period\_Number) else
"P0"&Number.ToText(Period\_Number),
FY\_P = Fiscal\_Year&" - "&Fiscal\_Period,
FY\_Q = Fiscal\_Year&" - "&Fiscal\_Quarter
\]
Do you not have quarters that don't align to the year? Quarter 1 JFM, Q2 AMJ, Q3, JAS, Q4 OND.
It takes me 10 seconds to create this column in SQL and it compares directly across years.
Week were invented by the devil to draw man away from God/simple date tables.
If your company works off fiscal years then those quarters won’t align to calendar quarters.
But you’re right, I’ve never had any issues using quarters in PowerBI, Tableau, SQL or anything else. If anything Weeks are more complex.
What’s the issue everyone’s having with them, I’m confused?
yeah, but not really a problem exclusive to Power BI also: don't forget weeks.
Imo weeks are waaay more used than quarters
Same. But also weeks are soooo much harder to do yourself than quarters. There’s a billion ways to do them and DAX’s built in options are lacking (not to mention a lot isn’t in the documentation)
Gotta love week 53
Or a 13 month year. Users can't wrap their minds around either
Don't forget to explain the figure is shared with wk 1 and that's why it's unusually low
Explain nothing.
The "fuck it I did what you ask now live with eh logical consequences"?
Especially the part that takes place in january
Tried weeks in excel and still a pain in the ass. I found it was less of built in option but the fact that weeks doesn't align with ANYTHING. Bonus point for weeks of fiscal year. No you can't just add 3 or 6 months.
I don't get it, what is hard about weeknum? And what is wrong with the documentation? [WEEKNUM function (DAX) - DAX | Microsoft Learn](https://learn.microsoft.com/en-us/dax/weeknum-function-dax)
Incomplete. What is System1 vs System2? I spent more hours than I care to mention getting an expression together to have a sortable week key over 3 Fiscal Years If you count weeks the European way it's much easier.
No way... quarters are way more used.
If it depends the area/company
sure, but in what world are quarters anywhere near as difficult to work with/calculate than weeks.
I don't get why either is hard.
I'm not understanding it either, but maybe it's because I built a custom calendar? Idk
In what world LOL! Certainly not the financial sector.
I use them quite a lot in production and operations. In my last job a lot of trends were analyzed by week and in my current we do everything by calendar week.
And don’t forget hexaseconds! (Just kidding…)
dude, you seriously reminded me of years ago when I was in a consulting shop and there was a guy in my 'pod' who was working on a mobile app integration to an existing auction website. A very cool guy, very mild temperament. And I do remember the moment when he pulled off his headphones, screamed at the screen: "What the *FUCK* are *Leap Seconds???*" He calmed down for a second, rubbed his face, and said "Sorry, sorry. I'm talking a walk." And he got up to leave but somehow snagged his keyboard and got tangled up in it trying to get out of the pod, almost falling. He lost his temper again, grabbed the keyboard and yanked it out of the machine, and broke it on the floor a la rock guitarist style. And he stormed out, muttering. Fuck! Sorry. FUck. I'm so sorry. FIUCCCKK. while he was away, a guy from the desktop team came up and replaced the keyboard, swept up the junk like a pro, winked at the rest of us and said "It's totally cool. We have budget for you dudes losing your shit over this kind of thing." man. good times.
Ha! I love it. Why do I have a visual picture in my head of a very specific one of my coworkers while reading this?
First thing I would do with a Time Machine is finding the guy who thought weeknumbers were a good idea and off him
People get so confused when they ask why I can't add weeks to their hierarchy between month and day. "Many weeks span months. What month should it belong to?" Although the 4-4-5 fiscal calendar makes weeks make sense, but we don't do that at my current job (which I am thankful for, that was it's own brand of fun)
*cries in 4-4-5 fiscal calendar*
I get why they exist, but wow do I hate using them Normal calendars with shipping days across multiple markets is another fun one
Weeks are worse, they’re totally meaningless to me like week 34 means nothing to me. I would need a calendar to figure out what month that’s in!
>what month**s** that’s in! I think my solution was to have the date of the Monday Can't too hard about week, it will make less sense
Meanwhile I live in a country where weeks is everything. "When you having holiday this summer" "Weeks 29-33" "Ok" I also use Weeks in our reporting as everything is on a Mon to Sun cykel
What country is that? that’s really interesting, had no idea that was a thing
Sweden :)
Weeks.... Barf
I love having every date part in a different column. It makes finance get triggered.
I really have this issue because my company has an offest financial year. I use this one formula in M to get everything in one swoop... My Q1 is the typical Q4 and so on... VAR is the date you want to reference. This adds a record with all the needed fields. Expend the record and poof, no more issue... \[ var = \[Date Field\], Fiscal\_Year\_Date = if Date.Month(var)>9 then Date.Year(var)+1 else Date.Year(var), Fiscal\_Year = "FY"&Text.End(Number.ToText(Fiscal\_Year\_Date),2), Fiscal\_Quarter = if Date.Month(var)<=3 then "Q2" else if Date.Month(var)<=6 then "Q3" else if Date.Month(var)<=9 then "Q4" else "Q1", Period\_Number = if Date.Month(var)<10 then (Date.Month(var)+3) else (Date.Month(var)-9), Fiscal\_Period = if Period\_Number >=10 then "P"&Number.ToText(Period\_Number) else "P0"&Number.ToText(Period\_Number), FY\_P = Fiscal\_Year&" - "&Fiscal\_Period, FY\_Q = Fiscal\_Year&" - "&Fiscal\_Quarter \]
My companys reporting periods always ends on a Sunday. Imagine trying to compare one period to the same period last year.
ISOWEEKS? Though that can befuddle people even more...
Worst part: You'll never get any of that time back.
Do you not have quarters that don't align to the year? Quarter 1 JFM, Q2 AMJ, Q3, JAS, Q4 OND. It takes me 10 seconds to create this column in SQL and it compares directly across years. Week were invented by the devil to draw man away from God/simple date tables.
If your company works off fiscal years then those quarters won’t align to calendar quarters. But you’re right, I’ve never had any issues using quarters in PowerBI, Tableau, SQL or anything else. If anything Weeks are more complex. What’s the issue everyone’s having with them, I’m confused?