cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MH3
Post Prodigy
Post Prodigy

Customize Calender Table Using M-QUERY

Hello Everyone!

I want to make a customized calendar table using M-QEURY for a Hotel Management System,

which Starts at 1st of May  and Ends on 30th of April,with Days, Weeks and months.

There are two Seasons which are Winter and Summer, and their respective months, weeks and days.

The whole year is divided in to two parts of 27 weeks.

 

Kindly help

 

Thanks!

 

 

 

10 REPLIES 10
amitchandak
Helper III
Helper III

@ImkeF , Can you help?

 

Please help me in this Calender Table Creating!!!
@amitchandak 
@ImkeF 

@MH3 , can you share in some excel. We will try to give logic in powerbi.

You can load to one drive or dropbox and share like.

@MH3 - please see this article on creating a date table in M, and optionally making it dynamic so it moves with your dates.

 

Unfortunately you will not be able to use many of the M Date.* functions as they rely on a calendar year. You'll need to create custom calculations to get the data you want. FOr example, your Q1 is May 1 through July 31. You could use something like:

 

 

= Table.AddColumn(#"Changed Type", "Quarter", 
    each let varDate = Date.Month([Date])
    in
    (if varDate >=5 and varDate <=7 then 1 else
    if varDate >=8 and varDate <=10 then 2 else
    if (varDate >=11 and varDate <=12) or varDate = 1 then 3 else
    4), Int64.Type)

 

That will return your quarter numbers properly - May-Jul Q1, Aug-Oct Q2, Nov-Jan Q3, Feb-Apr Q4.

 

You are going to have issues with Time Intelligence in DAX as well as it relies on either a calendar year or fiscal quarters that end in March, June, September, and December. Matt Allington has an excellent chapter on how to do this in his Super Charge Power BI book. A brief overview is here.
Here is my full M code you can paste into a blank query in Power Query to see the dates and quarters as done above. The Date* functions like Month Name and Day Name will work just fine. Just not the ones that automatically calculate things like quarter number, day in year, week in year, etc.

let
    Source = {Number.From(#date(2020,5,1))..Number.From(#date(2021,4,30))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Quarter", 
    each let varDate = Date.Month([Date])
    in
    (if varDate >=5 and varDate <=7 then 1 else
    if varDate >=8 and varDate <=10 then 2 else
    if (varDate >=11 and varDate <=12) or varDate = 1 then 3 else
    4), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Added Custom", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Days in Month" = Table.AddColumn(#"Inserted Month Name", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Days in Month", "Day Name", each Date.DayOfWeekName([Date]), type text)
in
    #"Inserted Day Name"

Hi @MH3  

alternatively, you could create a "DummyDate"-column that shifts the official dates 4 months back:

 

Date.AddMonths([Date],-4)

This would allow to use many standard Date-functions from Power Query.

 

The weekly logic of course requires more specification from your side. As @amitchandak said: Please post sample data and make especially clear how you'd like to handle weeks around the annual changes.

 

Capture.PNG

 

I hope this helps you well, it's just a Mock up, but I want to get something like this.

MH3
Post Prodigy
Post Prodigy

I'll provide you another Screenshot tomorrow.

@ImkeF 

@MH3 

Thanks, and please add sample data as html-tables as well (like described here: https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-... )

 

You might also find this interesting: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 

Be specific with your before- and after-samples.

 

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Users online (1,717)