Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pmstahl
Frequent Visitor

Custom Fiscal Calendar Look Up

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?

 

1 ACCEPTED 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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
edhans
Super User
Super User

See 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.

 

2020-05-29 13_24_51-20200529 - Fiscal Calendars - Power Query Editor.png

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans 

Thank 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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.