Custom Date table

[Anonymous]

Hi guys,

Is there a way that I can create a custom date table which has it's start date from 12/31/2018? My team wants the Date table to be in sync with the Fiscal Year Dates and want to see the data in the Fiscal format. So, when they select the month "Jan", instead of the typical date range of 1/1/2019 to 31/1/2019, I want it to show the data from 31/1/2019 to 2/3/2019 - which is the first Fiscal month "Jan".

Is there a way I can do that?

Thanks,

J 🙂

[v-piga-msft]

Hi @Anonymous,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best Regards,

Cherry

[Anonymous]

Hi Cherry,

Yes, that did solve my problem! Thank you so much for your help!

J

[v-piga-msft]

Hi @Anonymous,

It's glad that we can help.

If it is convenient, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Cherry

[v-piga-msft]

Hi @Anonymous,

You could create a custom date table which has it's start date from 12/31/2018 with the query below.

You could copy and paste in your Advanced Editor in Query Editor.

Spoiler

let


EndFiscalYearMonth = 6, //set this as the last month number of your fiscal year : June = 6, July =7 etc


StartDate= #date(2018,12,31), // Change start date #date(yyyy,m,d)
EndDate = DateTime.LocalNow(), // Could change to #date(yyyy,m,d) if you need to specify future date


/* Comment out the above StartDate and EndDate using // if you want to use a dynamic start and end date based on other query/table
You will need to change "Sales" and "Invoice Date" in 2 lines below and then remove the //
*/


//TableName = Sales
//DateColumnName = "Invoice Date"
//StartDate = Record.Field ( Table.Min(TableName,DateColumnName) ,DateColumnName),
//EndDate = Record.Field(Table.Max(TableName,DateColumnName),DateColumnName),

DateList = List.Dates(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)),

#"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Named as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Named as Date",{{"Date", type date}})
in
#"Changed Type"

In addition, you could have a reference of this similar thread which has been solved.

Best Regards,

Cherry

[ChrisMendoza]

@Anonymous-

Something like the below should help in your desired outcome (this is setup for my organizations FY; you'll need to modify for yours):

let
    Calendar = #table(
    type table
        [
            #"PeriodStart" = date,
            #"PeriodEnd" = date
        ],
    {
                { #date ( 2017, 06, 01 ), #date ( 2017, 6, 30 ) },
		{ #date ( 2017, 07, 01 ), #date ( 2017, 8, 1 ) },
		{ #date ( 2017, 08, 02 ), #date ( 2017, 8, 31 ) },
		{ #date ( 2017, 09, 01 ), #date ( 2017, 9, 30 ) },
		{ #date ( 2017, 10, 01 ), #date ( 2017, 10, 31 ) },
		{ #date ( 2017, 11, 01 ), #date ( 2017, 11, 30 ) },
		{ #date ( 2017, 12, 01 ), #date ( 2017, 12, 31 ) },
		{ #date ( 2018, 01, 01 ), #date ( 2018, 1, 30 ) },
		{ #date ( 2018, 01, 31 ), #date ( 2018, 2, 28 ) },
		{ #date ( 2018, 03, 01 ), #date ( 2018, 3, 31 ) },
		{ #date ( 2018, 04, 01 ), #date ( 2018, 4, 30 ) },
		{ #date ( 2018, 05, 01 ), #date ( 2018, 5, 30 ) },
		{ #date ( 2018, 05, 31 ), #date ( 2018, 6, 30 ) },
		{ #date ( 2018, 07, 01 ), #date ( 2018, 7, 31 ) },
		{ #date ( 2018, 08, 01 ), #date ( 2018, 8, 30 ) },
		{ #date ( 2018, 08, 31 ), #date ( 2018, 9, 30 ) },
		{ #date ( 2018, 10, 01 ), #date ( 2018, 10, 30 ) },
		{ #date ( 2018, 10, 31 ), #date ( 2018, 11, 29 ) },
		{ #date ( 2018, 11, 30 ), #date ( 2018, 12, 31 ) },
		{ #date ( 2019, 01, 01 ), #date ( 2019, 1, 30 ) },
		{ #date ( 2019, 01, 31 ), #date ( 2019, 2, 28 ) },
		{ #date ( 2019, 03, 01 ), #date ( 2019, 3, 31 ) },
		{ #date ( 2019, 04, 01 ), #date ( 2019, 4, 30 ) },
		{ #date ( 2019, 05, 01 ), #date ( 2019, 5, 30 ) },
		{ #date ( 2019, 05, 31 ), #date ( 2019, 6, 30 ) }
    }
),
    #"Added Index" = Table.AddIndexColumn(Calendar, "PeriodID", 1, 1),
    #"Added DatesBetween" = Table.AddColumn(#"Added Index", "Date", each List.Transform( { Number.From ( [PeriodStart] ) ..Number.From ( [PeriodEnd] ) }, each Date.From (_) ) ),
    #"Expanded Date" = Table.SelectColumns(Table.TransformColumnTypes(Table.ExpandListColumn(#"Added DatesBetween", "Date"),{{"Date", type date}}),{"PeriodID", "Date"}),
    #"Expanded EndOfWeek" = Table.TransformColumnTypes(Table.SelectColumns(Table.ExpandTableColumn(Table.AddIndexColumn(Table.Group( Table.AddColumn(#"Expanded Date", "End of Week", each Date.EndOfWeek([Date]), type date), {"End of Week"}, {{"EndOfWeek", each _, type table}}), "EndOfWeekID", 1, 1), "EndOfWeek", {"PeriodID", "Date"}, {"PeriodID", "Date"}),{"PeriodID", "EndOfWeekID", "Date" }),{{"PeriodID", Int64.Type},{"EndOfWeekID", Int64.Type},{"Date", type date}}),
    #"Added FiscalYearPeriodNum" = Table.AddColumn(#"Expanded EndOfWeek", "FiscalYearPeriodNum", each if Number.Mod([PeriodID], 12) = 0 then 12 else Number.Mod([PeriodID], 12), Int64.Type),
    #"Added FiscalYearPeriodName" = Table.AddColumn(#"Added FiscalYearPeriodNum", "FiscalYearPeriodName", each fnSwitchPeriodNumToName([FiscalYearPeriodNum]), type text),
    #"Added FiscalYearNum" = Table.AddColumn(#"Added FiscalYearPeriodName", "Fiscal Year", each if [FiscalYearPeriodNum] <= 7 then Date.Year([Date]) else Date.Year([Date]) - 1, Int64.Type),
    #"Added FiscalYearPeriodID" = Table.AddColumn(#"Added FiscalYearNum", "FiscalYearPeriodID", each ([Fiscal Year] * 100) + [FiscalYearPeriodNum], Int64.Type),
    #"Added FiscalYearPeriodCombined" = Table.RemoveColumns(Table.AddColumn(#"Added FiscalYearPeriodID", "Fiscal Year Period", each Text.Combine({Text.PadStart(Number.ToText([FiscalYearPeriodNum]), 2, "0"), [FiscalYearPeriodName]}, "-"), type text),{"FiscalYearPeriodNum", "FiscalYearPeriodName"})
in
    #"Added FiscalYearPeriodCombined"

You may also need the function below:

(input) =>

let

values = {

{1, "Jul"},
{2, "Aug"},
{3, "Sep"},
{4, "Oct"},
{5, "Nov"},
{6, "Dec"},
{7, "Jan"},
{8, "Feb"},
{9, "Mar"},
{10, "Apr"},
{11, "May"},
{12, "Jun"},
{input, "Undefined"}

},

Result = List.First(List.Select(values, each _{0}=input)){1}

in

Result

[AlB]

Hi @Anonymous

I don't understand well what days each month would include. Can you explain it a bit more? Are you using Month/Day/Year or Day/Month/Year notation? You seem to be showing examples of both in your explanation.  

[ChrisMendoza]

@Anonymous- My approach to this type of custom need below (unfortunately manually inserting new defined periods is downside):

let
    Calendar = #table(
    type table
        [
            #"PeriodStart" = date,
            #"PeriodEnd" = date
        ],
    {
        { #date ( 2019, 1, 1 ), #date ( 2019, 2, 3 ) },
    { #date ( 2019, 2, 4 ), #date ( 2019, 3, 2 ) }
    }
),
    #"Added Index" = Table.AddIndexColumn(Calendar, "PeriodID", 1, 1),
    #"Added DatesBetween" = Table.AddColumn(#"Added Index", "Date", each List.Transform( { Number.From ( [PeriodStart] ) ..Number.From ( [PeriodEnd] ) }, each Date.From (_) ) ),
    #"Expanded Date" = Table.SelectColumns(Table.TransformColumnTypes(Table.ExpandListColumn(#"Added DatesBetween", "Date"),{{"Date", type date}}),{"PeriodID", "Date"}),
    #"Expanded EndOfWeek" = Table.TransformColumnTypes(Table.SelectColumns(Table.ExpandTableColumn(Table.AddIndexColumn(Table.Group( Table.AddColumn(#"Expanded Date", "End of Week", each Date.EndOfWeek([Date]), type date), {"End of Week"}, {{"EndOfWeek", each _, type table}}), "EndOfWeekID", 1, 1), "EndOfWeek", {"PeriodID", "Date"}, {"PeriodID", "Date"}),{"PeriodID", "EndOfWeekID", "Date" }),{{"PeriodID", Int64.Type},{"EndOfWeekID", Int64.Type},{"Date", type date}}),
    #"Added FiscalYearPeriodNum" = Table.AddColumn(#"Expanded EndOfWeek", "FiscalYearPeriodNum", each if Number.Mod([PeriodID], 12) = 0 then 12 else Number.Mod([PeriodID], 12), Int64.Type),
    #"Added FiscalYearPeriodName" = Table.AddColumn(#"Added FiscalYearPeriodNum", "FiscalYearPeriodName", each fnSwitchPeriodNumToName([FiscalYearPeriodNum]), type text),
    #"Added FiscalYearNum" = Table.AddColumn(#"Added FiscalYearPeriodName", "Fiscal Year", each if [FiscalYearPeriodNum] <= 7 then Date.Year([Date]) else Date.Year([Date]) - 1, Int64.Type),
    #"Added FiscalYearPeriodID" = Table.AddColumn(#"Added FiscalYearNum", "FiscalYearPeriodID", each ([Fiscal Year] * 100) + [FiscalYearPeriodNum], Int64.Type),
    #"Added FiscalYearPeriodCombined" = Table.RemoveColumns(Table.AddColumn(#"Added FiscalYearPeriodID", "Fiscal Year Period", each Text.Combine({Text.PadStart(Number.ToText([FiscalYearPeriodNum]), 2, "0"), [FiscalYearPeriodName]}, "-"), type text),{"FiscalYearPeriodNum", "FiscalYearPeriodName"})
in
    #"Added FiscalYearPeriodCombined"

As long as your period start and end are truly consecutive you should end up with every day in your 'Calendar' that is redefined by subsequent column [Fiscal Year Period]