Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
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
Hi Cherry,
Yes, that did solve my problem! Thank you so much for your help!
J
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
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.
In addition, you could have a reference of this similar thread which has been solved.
Best Regards,
Cherry
@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
Proud to be a Super User!
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.
@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]
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |