Hi @richmont,
I’m sure I can help you with that, could you create a new topic and provide some additional details on how the week numbers should be handled at the calendar year start and end?
Please link to this post OR @ mention me, thanks.
Hi @richmont,
I’m sure I can help you with that, could you create a new topic and provide some additional details on how the week numbers should be handled at the calendar year start and end?
Please link to this post OR @ mention me, thanks.
HI @richmont ,
Sorry for the confusion.
@Melissa,
I would also like to know this answer too, just in case i run up against it in the future.
thanks
Keith
@Melissa Thank you Melissa and for all you do! Here in the US, many companies use a standard Gregorian calendar for their “fiscal year”, including my company. Jan 1 to Dec 31.
It would be great to have a version of your date table based on this. We would define Sunday as the start of the week (not the first workday necessarily, that would still be Monday). But a week would be defined as Sunday thru Saturday. I’m sure many readers here in the US would find a Gregorian based date table useful.
Two other related things:
Hi @richmont,
Thanks for your reply, I am away now but expect to get back to you later today, tomorrow at the latest.
It’s actually a piece of redundant code. Formatted it makes more sence, I think.
From the inside out; I’m getting the weekday name of a specific date, keep its first 3 letters and have that start with a capital.
@Melissa Many thanks! One question. I am getting odd behavior with the Year & Month, Quarter & Month, and Week & Month. I have two models, one for sales, and one for quality issues. The quality issues model includes a direct query dataset (from the sales model), and it is in Mixed mode. When I use those fields on a chart visual, I get a NaN error. I do not get this error on the fully import mode model (the sales model). But I do get it on the mixed mode model (the quality dataset). It only seems to apply to those fields. I can bring in quarter, week, etc with no errors. Any idea what is causing this and how to fix?
Hi @Melissa was concerned about the application of the formula, provided by @sam.mckay in his article about
When applying it accoding to the fields of your “Date Table” it does not seem to work and I am not able to understand what is wrong.
Thanks in advance!
Hi @Jose,
Welcome to the Forum!
I’d be happy to look into this for you, can you please create a new Topic, supply your work in progress file with a mock up of the desired outcome.
See this great reference on how to do that.
https://forum.enterprisedna.co/t/tip-tools-and-techniques-for-providing-pbix-files-with-your-forum-questions/17763
Sure! thanks How can i create a new topic, thouhght I had already created one by posting the message?
Hey Jose,
hope all is good with you
You’ve created an additional message/post on an existing thread
If you go to this link Latest topics - Enterprise DNA Forum and select the New Topic button to create a new topic.
Not a problem & welcome to the Forum
Hello Melissa,
In your date table, I think it’s already there but I’m not sure …
What, I’m looking for is workdays in a month.
July 2021
It had 31 days and 22 work days.
I think your Calendar has this already, can you just point me in the right direction please.
Thanks
@ericet ,
You can use the IsBusinessDay or IsWorkingDay fields to calculate this. The former is false on holidays and weekends, while the latter is false only on weekends.
With DAX, it’s a simple CALCULATE ( COUNTROWS( Dates), Dates[IsBusinessDay] = TRUE) set against the proper monthly context to give business days per month (or can substitute working days).
If you want to do this in PQ, just use an AllRows/GroupBy combo on Month & Year and IsBusinessDay or IsWorkingDay, and count the rows where the IsBusinessDay or IsWorkingDay is TRUE. This video walks you through that process:
I hope this is helpful.
– Brian
Thank you Brian, that works great. But another question in my data I don’t have all the days in a month
so I cannot count that.
Now, I’m looking at the Extended Date Table and trying to find how many days in a month ?
I’m surprised that I did not see it. Did I miss something ?
How many days in a month ?
@ericet ,
Here’s a simple way to do that without having to modify the M code. When you expand this, it will add a Days in Month column to your Dates Table.
– Brian
I looked at your video, but I was looking something even easier
Found this solution: (Still checking if it’s OK at first glance seems to work.)
Number of Days in a Month =
DATEDIFF(
EOMONTH(MAX(atWorkData[YYYY-MM-DD]), -1),
EOMONTH(MAX(atWorkData[YYYY-MM-DD]), 0), DAY
)