Forum Discussion

pmstahl's avatar
pmstahl
Frequent Visitor
6 years ago
Solved

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?

 

Tips and Tricks
  • edhans's avatar
    edhans
    6 years ago

    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

     

3 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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.

     

     

    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.

    • pmstahl's avatar
      pmstahl
      Frequent Visitor
      6 years ago

      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?

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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