Fiscal Calendar

Hi,

I am just beginning with Power BI.

I have watched a lot of videos also including reading the notes and the calendar that is provided through this website.

My question is how do you adapt a date calendar to specific dates do not follow calendar month of the 4-4-5 retail calendar.

Any help would be greatly appreciated.

Thanks

Suzie
Fiscal Calendar.xlsx (11.0 KB)

Hi @Suzie,

Can’t unravel the logic that defines these periods but since you’ve got a period start- and end date you can built a proper Date dimension table by expanding it to a daily granularity.

Once that’s done, you add additional date attributes to meet your requirements.
Paste this code into a new blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZLLasMwEEX/xesINFeWZC8LpYtA20WXxos0hG5CG0pT6N/XkuZhLQLD5eQy8pxlGY6nTwcaDkP5wQNljI7g4EsSnacCrIdleLq8NxbGIjGwjcF5CPt8+m5p2LEjA+1vQdiHG7Pjjs0MbA3e+dF6/1oajd0W8JFZGQt7vPPbUs8m3iHy2NhrS3P/tmS9Wfe9f7R06nsnYydh3y63ls49O/MOmcfCvp5/+BZ+t8Sk8FZMXuCXr1+GqWsm0q9GernHy5lh9DCfgy/On6IoAVOiXJCSsuV5VABVAqZE4OfB6fIQtioBU0LYwKmMqgRMCWFHZj2PqgRMCWGj9UZhqxIwJYRN1puMvbY092y23qz7FiVgSgg7GTsJW5WAKSHsbDvMwlYlsFOC4eIBTAmYEtgpITBpM+nlqhLYKSEwrFlPV5UIpsTIK1NLg4zr+g8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, #"Sap BCS " = _t, #"Sap BCS Year" = _t, #"SAP Period Start Date" = _t, #"SAP Period End Date" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"SAP Period Start Date", type date}, {"SAP Period End Date", type date}, {"Sap BCS Year", Int64.Type}, {"Sap BCS ", Int64.Type}}),
    CreateDatesList = Table.AddColumn(ChType, "Date", each List.Dates( [SAP Period Start Date], Number.From( [SAP Period End Date] - [SAP Period Start Date] ) +1, Duration.From( 1 ))),
    ExpandDate = Table.ExpandListColumn(CreateDatesList, "Date")
in
    ExpandDate

I hope this is helpful

1 Like

Thank you so much for that detailed answer @Melissa We hope this helped you @Suzie :slight_smile:

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

Hi - here is tutorial on creating a calendar

from there you could put rules in place to get what you need using custom columns