Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am looking for a solution that will generate a calendar look up table for my company's fiscal calendar.
Our fiscal weeks run from Saturday - Friday.
The fiscal year begins the Saturday after the last Friday in November.
Example:
2016 Fiscal Year: Saturday 12/5/2015 - 12/2/2016
2017 Fiscal Year: Saturday 12/3/2016 - 12/1/2017
2018 Fiscal Year: Saturday 12/2/2017 - 11/30/3018
2019 Fiscal Year: Saturday 12/1/2018 - 11/29/2019
2020 Fiscal Year: Saturday 11/30/2019 - 12/4/2020
Is something like this possible in the Advanced Editor or DAX?
Solved! Go to Solution.
How would you do that in Excel? In othe words, how do you count your weeks? We could implement just about any similar logic in Power Query to count the weeks.
EDIT: Had a thought: See if this code works. use this for the date table, or go get the PBIX file I linked to above. It has been updated.
let
Source = {Number.From(#date(2015,12,5))..Number.From(#date(2020,12,4))},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
#"Appended Query" = Table.Combine({#"Changed Type", #"Fiscal Calendar Start Date"}),
#"Sorted Rows" = Table.Sort(#"Appended Query",{{"Date", Order.Ascending}, {"Fiscal Year", Order.Descending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"Fiscal Year"}),
#"Removed Duplicates" = Table.Distinct(#"Filled Down"),
#"Inserted Year" = Table.AddColumn(#"Removed Duplicates", "Calendar Year", each Date.Year([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Year", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Added Fiscal Week" =
Table.AddColumn(
#"Inserted Day Name",
"Fiscal Week",
each
let
varCurrentDate = [Date],
varFiscalYear = [Fiscal Year]
in
Number.RoundUp(
Table.RowCount(
Table.SelectRows(#"Inserted Day Name", each [Fiscal Year] = varFiscalYear and [Date] <= varCurrentDate)
) / 7
)
)
in
#"Added Fiscal Week"
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSee if this helps. It returns a table that starts to look like this. You can and should add other columns to make your date table complete, including an index field, which is helpful in date intelligence functions for fiscal years.
The file is here. You will need to look in Power Query to see how it works. It is a modified version of this pattern.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you for taking the time to help me! It is greatly appreciated.
How would I create a column that would have the Fiscal Week Numbered 1-52 and in some years 53?
How would you do that in Excel? In othe words, how do you count your weeks? We could implement just about any similar logic in Power Query to count the weeks.
EDIT: Had a thought: See if this code works. use this for the date table, or go get the PBIX file I linked to above. It has been updated.
let
Source = {Number.From(#date(2015,12,5))..Number.From(#date(2020,12,4))},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
#"Appended Query" = Table.Combine({#"Changed Type", #"Fiscal Calendar Start Date"}),
#"Sorted Rows" = Table.Sort(#"Appended Query",{{"Date", Order.Ascending}, {"Fiscal Year", Order.Descending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"Fiscal Year"}),
#"Removed Duplicates" = Table.Distinct(#"Filled Down"),
#"Inserted Year" = Table.AddColumn(#"Removed Duplicates", "Calendar Year", each Date.Year([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Year", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Added Fiscal Week" =
Table.AddColumn(
#"Inserted Day Name",
"Fiscal Week",
each
let
varCurrentDate = [Date],
varFiscalYear = [Fiscal Year]
in
Number.RoundUp(
Table.RowCount(
Table.SelectRows(#"Inserted Day Name", each [Fiscal Year] = varFiscalYear and [Date] <= varCurrentDate)
) / 7
)
)
in
#"Added Fiscal Week"
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
60 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |