You’re offline. This is a read only version of the page.
1,057
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!
@MH3 ,Please find the calendar generated using DAX
https://www.dropbox.com/s/rxhq0ko80zejlxq/May2Apr-Cal_FY_calendar.pbix?dl=0
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-p/963216 )
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.
Hi @MH3
alternatively, you could create a "DummyDate"-column that shifts the official dates 4 months back:
Share
Report
All responses (
Answers (
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.
@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"@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.
Please help me in this Calender Table Creating!!!
@amitchandak
@ImkeF
#1
mmbr1606
9
Super User 2025 Season 1
#2
stampcoin
7
#3
SD-13050734-0
6
