@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:
You have a weekending clause already. Could you possibly add a weekstarting one (to both versions)?
In the ISO code version, what does this line mean? Specifically, the last part in parenthesis.
StartOfWeekDayName = Text.Proper( Text.Start( Date.DayOfWeekName( #date(2021, 2,1) ), 3)),
@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?
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:
Hi @Melissa ,
I want to have Latest 4,12,26 and 52 weeks in a slicer which can filter me the data correspondingly from a table. I am not sure how to do it. But I am using our custom table only. Kindly help me with this.