Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MH3
Helper V
Helper V

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
Super User
Super User

@ImkeF , Can you help?

 

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

@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"


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@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.

Capture.PNG

 

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

Sure, 

I'll check it out 

@amitchandak 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors