Suzie
January 13, 2022, 10:37pm
1
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
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