cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aliah-
Frequent Visitor

Custom Calendar/Date according to Accounting Period

Hi,

I'm trying to format my calendar according to my company's accounting period.

Example, from 26/11/2020 -25/12/2020, this will considered as December 2020.
From 26/12/2020 - 25/1/2021, this will be considered as January 2021.

I've tried to think of a way but couldn't get it right. Anyone have experience with this and can share with me?

Thanks a lot.

1 ACCEPTED SOLUTION
AllisonKennedy
Super User III
Super User III

@aliah- 

 

Please see if this Power Query code will work for you, adapted from this site; https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

 

 

let
startDate = #date(2019, 1, 1),
// Edit the number in this step to change the number of months after today for the last day of the Calendar table.
endDate = Date.AddMonths(Date.From(DateTime.LocalNow()),3),
Dates = List.Dates(startDate, Duration.Days(endDate - startDate), #duration (1,0,0,0)),
#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted DateKey" = Table.AddColumn(#"Changed Type", "DateKey", each Date.ToText([Date],"yyyyMMdd"), type text),
#"Inserted Year" = Table.AddColumn(#"Inserted DateKey", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Inserted FY Quarters" = Table.AddColumn(#"Inserted Quarter", "FY Quarter", each if [Quarter] = 1 then "4" else if [Quarter] = 2 then "1" else if [Quarter] = 3 then "2" else "3", type text),
#"Inserted Month Name" = Table.AddColumn(#"Inserted FY Quarters", "Month name", each Date.MonthName([Date]), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Month Name", "Month number", each Date.Month([Date]), Int64.Type),
#"Inserted Day of Month" = Table.AddColumn(#"Inserted Month", "Day of month", each Date.Day([Date]), Int64.Type),
#"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Month", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day of Year", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day name", each Date.DayOfWeekName([Date]), type text),
// In Week functions
// 0 represents Sunday start
// 1 represents Monday start
// 2 represents Tuesday start
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Day Name", "Week of Year", each Date.WeekOfYear([Date],1), Int64.Type),
#"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],1), Int64.Type),
#"Inserted FY start" = Table.AddColumn(#"Inserted Week of Month", "FY starts", each [Year] + (if [Month number] > 3 then 0 else -1), type number),
#"Inserted FY" = Table.AddColumn(#"Inserted FY start", "FY", each Text.From([FY starts]) & "/" & Text.From([FY starts] + 1), type text),
#"Inserted FY and Quarter" = Table.AddColumn(#"Inserted FY", "FY and Quarter", each Text.Combine({Text.From([FY starts], "en-NZ"), [FY Quarter]}, " Q"), type text),
#"Inserted Merged Column" = Table.AddColumn(#"Inserted FY and Quarter", "FY and Quarter Sort", each Text.Combine({Text.From([FY starts]), [FY Quarter]}, ""), type text),
#"Inserted Custom Month Number" = Table.AddColumn(#"Inserted Merged Column", "Company Custom Month Number", each if [Day of month] <= 25 then [Month number] else ( if [Month number]<12 then [Month number] +1 else 1)),
#"Inserted Custom Year" = Table.AddColumn(#"Inserted Custom Month Number", "Company Custom Year", each if [Day of month] >= 26 and [Month number] =12 then [Year] +1 else [Year]),
#"Inserted Custom Month Name" = Table.AddColumn(#"Inserted Custom Year", "Company Custom Month Name", each if [Company Custom Month Number] = 1 then "January" else if [Company Custom Month Number] = 2 then "February"
else if [Company Custom Month Number] = 3 then "March" else if [Company Custom Month Number] = 4 then "April" else if [Company Custom Month Number] = 5 then "May" else if [Company Custom Month Number] = 6 then "June" else if [Company Custom Month Number] = 7 then "July" else if [Company Custom Month Number] = 8 then "August" else if [Company Custom Month Number] = 9 then "September" else if [Company Custom Month Number] = 10 then "October" else if [Company Custom Month Number] = 11 then "November" else "December"),
#"Inserted Custom Month Year" = Table.AddColumn(#"Inserted Custom Month Name", "Company Custom Month Year", each Text.Combine({[Company Custom Month Name], Text.From([Company Custom Year], "en-US")}, " "), type text)
in
#"Inserted Custom Month Year"



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

View solution in original post

5 REPLIES 5
amitchandak
Super User IV
Super User IV

@aliah- , refer My video for startof month and end of month (custom) -https://www.youtube.com/watch?v=Ro0E-4Wg5-A

https://www.youtube.com/watch?v=TO4vtDz2bss

 

And for custom FY -https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

StefanoGrimaldi
Solution Sage
Solution Sage

here you go, this will set the between condition you looking forby specific intervals and assign the period, if isnt setted the condition wil tell no parameterss to define accountable period

StefanoGrimaldi_0-1609117947731.png

 

create a new date table with the range of the calendar or autocalendar and set up this column to define the accountable period by date. 

 

AllisonKennedy
Super User III
Super User III

@aliah- 

 

Please see if this Power Query code will work for you, adapted from this site; https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

 

 

let
startDate = #date(2019, 1, 1),
// Edit the number in this step to change the number of months after today for the last day of the Calendar table.
endDate = Date.AddMonths(Date.From(DateTime.LocalNow()),3),
Dates = List.Dates(startDate, Duration.Days(endDate - startDate), #duration (1,0,0,0)),
#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted DateKey" = Table.AddColumn(#"Changed Type", "DateKey", each Date.ToText([Date],"yyyyMMdd"), type text),
#"Inserted Year" = Table.AddColumn(#"Inserted DateKey", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Inserted FY Quarters" = Table.AddColumn(#"Inserted Quarter", "FY Quarter", each if [Quarter] = 1 then "4" else if [Quarter] = 2 then "1" else if [Quarter] = 3 then "2" else "3", type text),
#"Inserted Month Name" = Table.AddColumn(#"Inserted FY Quarters", "Month name", each Date.MonthName([Date]), type text),
#"Inserted Month" = Table.AddColumn(#"Inserted Month Name", "Month number", each Date.Month([Date]), Int64.Type),
#"Inserted Day of Month" = Table.AddColumn(#"Inserted Month", "Day of month", each Date.Day([Date]), Int64.Type),
#"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Month", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day of Year", "Day of Week", each Date.DayOfWeek([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day name", each Date.DayOfWeekName([Date]), type text),
// In Week functions
// 0 represents Sunday start
// 1 represents Monday start
// 2 represents Tuesday start
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Day Name", "Week of Year", each Date.WeekOfYear([Date],1), Int64.Type),
#"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],1), Int64.Type),
#"Inserted FY start" = Table.AddColumn(#"Inserted Week of Month", "FY starts", each [Year] + (if [Month number] > 3 then 0 else -1), type number),
#"Inserted FY" = Table.AddColumn(#"Inserted FY start", "FY", each Text.From([FY starts]) & "/" & Text.From([FY starts] + 1), type text),
#"Inserted FY and Quarter" = Table.AddColumn(#"Inserted FY", "FY and Quarter", each Text.Combine({Text.From([FY starts], "en-NZ"), [FY Quarter]}, " Q"), type text),
#"Inserted Merged Column" = Table.AddColumn(#"Inserted FY and Quarter", "FY and Quarter Sort", each Text.Combine({Text.From([FY starts]), [FY Quarter]}, ""), type text),
#"Inserted Custom Month Number" = Table.AddColumn(#"Inserted Merged Column", "Company Custom Month Number", each if [Day of month] <= 25 then [Month number] else ( if [Month number]<12 then [Month number] +1 else 1)),
#"Inserted Custom Year" = Table.AddColumn(#"Inserted Custom Month Number", "Company Custom Year", each if [Day of month] >= 26 and [Month number] =12 then [Year] +1 else [Year]),
#"Inserted Custom Month Name" = Table.AddColumn(#"Inserted Custom Year", "Company Custom Month Name", each if [Company Custom Month Number] = 1 then "January" else if [Company Custom Month Number] = 2 then "February"
else if [Company Custom Month Number] = 3 then "March" else if [Company Custom Month Number] = 4 then "April" else if [Company Custom Month Number] = 5 then "May" else if [Company Custom Month Number] = 6 then "June" else if [Company Custom Month Number] = 7 then "July" else if [Company Custom Month Number] = 8 then "August" else if [Company Custom Month Number] = 9 then "September" else if [Company Custom Month Number] = 10 then "October" else if [Company Custom Month Number] = 11 then "November" else "December"),
#"Inserted Custom Month Year" = Table.AddColumn(#"Inserted Custom Month Name", "Company Custom Month Year", each Text.Combine({[Company Custom Month Name], Text.From([Company Custom Year], "en-US")}, " "), type text)
in
#"Inserted Custom Month Year"



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

View solution in original post

Hi @AllisonKennedy ,

This works well. Thanks!

@aliah-  You're welcome. Glad you found it useful. 



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors