Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi, How can I create a calendar table with each month having 30 days. For example January to be from Jan 1 to Jan 30, February to be from Jan 31 to March 1 , March to be fromMarch 2 to March 31 and so on
Solved! Go to Solution.
hi @Ephy ,
try like:
dates2 =
ADDCOLUMNS(
ADDCOLUMNS(
CALENDAR(DATE(2022,12,1), DATE(2023,3,1)),
"NewMonthNumber",
VAR _m= MONTH([date])
VAR _d = DAY([date])
RETURN
SWITCH(
TRUE(),
_m=1 && _d<=30, 1,
_m=2 || (_m=1&&_d=31 ) || (_m=3 && _d=1 ), 2,
_m=3&& _d>=2 && _d<=31, 3,
_m=4 || (_m=5&&_d=1 ), 4,
(_m=5&&_d>=2) || (_m=6&&_d=1 ), 5,
(_m=6&&_d>=2) || (_m=7&&_d=1 ), 6,
(_m=7&&_d>=2 ), 7,
(_m=8&&_d<=30 ), 8,
(_m=8&&_d=31) || _m=9, 9,
_m=10, 10,
_m=11, 11,
12
)
),
"NewMonth",
SWITCH(
[NewMonthNumber],
1, "Jan",
2, "Feb",
3, "Mar",
4, "Apr",
5, "May",
6, "Jun",
7, "Jul",
8, "Aug",
9, "Sep",
10, "Oct",
11, "Nov",
"Dec"
)
)
hi @Ephy ,
you may also try like:
dates =
ADDCOLUMNS(
ADDCOLUMNS(
CALENDAR(DATE(2022,12,1), DATE(2023,3,1)),
"NewMonthNumber",
VAR _days = [date] - DATE(YEAR([date]),1,1) +1
VAR _result = INT(DIVIDE(_days, 30))+1
RETURN _result
),
"NewMonth",
SWITCH(
[NewMonthNumber],
1, "Jan",
2, "Feb",
3, "Mar",
4, "Apr",
5, "May",
6, "Jun",
7, "Jul",
8, "Aug",
9, "Sep",
10, "Oct",
11, "Nov",
12, "Dec",
"Jan"
)
)
it worked like:
Hi @FreemanZ
thank you so much. But I have one more question my data starts from June 2 and when I put the min and max of my data it didn't give me the intervals. Is there something I need to tweak? Also, I apologize in advance I just found out that to include the rest of the days(5) the setup of the calendar should be
Jan ,from Jan 1- Jan 30
Feb, Jan 31-Mar 1
March, Mar 2-Mar 31
April, Apr 1- May 1
May, May 2- Jun 1
June, June 2-Jul 1
July, July 2-July 31
August, Aug 1-Aug 30
September, Aug 31- Sep 30
October, Oct 1- Oct 31
November, Nov 1 - Dec 1
December, Dec 2 - Dec 31
Is it possible to set it this way?
hi @Ephy ,
try like:
dates2 =
ADDCOLUMNS(
ADDCOLUMNS(
CALENDAR(DATE(2022,12,1), DATE(2023,3,1)),
"NewMonthNumber",
VAR _m= MONTH([date])
VAR _d = DAY([date])
RETURN
SWITCH(
TRUE(),
_m=1 && _d<=30, 1,
_m=2 || (_m=1&&_d=31 ) || (_m=3 && _d=1 ), 2,
_m=3&& _d>=2 && _d<=31, 3,
_m=4 || (_m=5&&_d=1 ), 4,
(_m=5&&_d>=2) || (_m=6&&_d=1 ), 5,
(_m=6&&_d>=2) || (_m=7&&_d=1 ), 6,
(_m=7&&_d>=2 ), 7,
(_m=8&&_d<=30 ), 8,
(_m=8&&_d=31) || _m=9, 9,
_m=10, 10,
_m=11, 11,
12
)
),
"NewMonth",
SWITCH(
[NewMonthNumber],
1, "Jan",
2, "Feb",
3, "Mar",
4, "Apr",
5, "May",
6, "Jun",
7, "Jul",
8, "Aug",
9, "Sep",
10, "Oct",
11, "Nov",
"Dec"
)
)
Hi,
Please check the below picture and the attached pbix file.
New Calendar =
VAR _calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2023, 12, 31 ) ),
"@monthnumber", MONTH ( [Date] ),
"@monthname", FORMAT ( [Date], "mmmm" )
)
VAR _datenumber =
ADDCOLUMNS (
_calendar,
"@datenumber", SUMX ( FILTER ( _calendar, [Date] <= EARLIER ( [Date] ) ), 1 )
)
VAR _newmonthnumber =
ADDCOLUMNS (
_datenumber,
"@newmonthnumber", ROUNDUP ( DIVIDE ( [@datenumber], 30 ), 0 )
)
VAR _month =
SUMMARIZE ( _calendar, [@monthnumber], [@monthname] )
VAR _newcalendar =
ADDCOLUMNS (
_newmonthnumber,
"@newmonthname",
MAXX (
FILTER ( _month, [@monthnumber] = EARLIER ( [@newmonthnumber] ) ),
[@monthname]
)
)
RETURN
SUMMARIZE ( _newcalendar, [Date], [@newmonthnumber], [@newmonthname] )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_Kim thank you so much. But I have one more question my data starts from June 2 and when I put the min and max of my data it didn't give me the intervals. Is there something I need to tweak? Also, I apologize in advance I just found out that to include the rest of the days(5) the setup of the calendar should be
Jan ,from Jan 1- Jan 30
Feb, Jan 31-Mar 1
March, Mar 2-Mar 31
April, Apr 1- May 1
May, May 2- Jun 1
June, June 2-Jul 1
July, July 2-July 31
August, Aug 1-Aug 30
September, Aug 31- Sep 30
October, Oct 1- Oct 31
November, Nov 1 - Dec 1
December, Dec 2 - Dec 31
Please let me know if there is a way to achieve this. Thank you
User | Count |
---|---|
21 | |
20 | |
15 | |
10 | |
7 |
User | Count |
---|---|
29 | |
28 | |
12 | |
12 | |
12 |