Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
@ImkeF , Can you help?
@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"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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
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.
I hope this helps you well, it's just a Mock up, but I want to get something like this.
@MH3 ,Please find the calendar generated using DAX
https://www.dropbox.com/s/rxhq0ko80zejlxq/May2Apr-Cal_FY_calendar.pbix?dl=0