cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Divide months from day 16 to 15

Good afternoon,

I need to analyze data over one-month periods. But the beginning of the month is always at 16.

Example:

January 16th to February 15th

February 16th to March 15th

March 16th to April 15th

Etc...

How can I do this?

Thanks.

1 ACCEPTED SOLUTION
Solution Sage

Drop this into DAX Studio and execute:

``````EVALUATE
var Dates =
SELECTCOLUMNS(
CALENDAR( "2020-01-01", "2021-12-31" ),
"@Date", [Date]
)
return
SELECTCOLUMNS(
Dates,
"@Bucket",
var CurrentDate = [@Date]
var CurrentMonth = month( CurrentDate )
var CurrentDay = day( CurrentDate )
var Bucket =
switch( true(),
15 < CurrentDay, CurrentMonth,
CurrentMonth - 1
)
return
Bucket
),
"Date", [@Date],
"Bucket",
"Month_" & format(
if( [@Bucket] = 0, 12, [@Bucket] ),
"00"
)
)``````
7 REPLIES 7
Frequent Visitor

I have a data table with:

Data; Day Name; Day of Week; MMM; Month; Month Name; Month ID; Year; YYMM

Do I have a simple way to do this categorization? Or do I have to create a table manually?

Solution Sage

Drop this into DAX Studio and execute:

``````EVALUATE
var Dates =
SELECTCOLUMNS(
CALENDAR( "2020-01-01", "2021-12-31" ),
"@Date", [Date]
)
return
SELECTCOLUMNS(
Dates,
"@Bucket",
var CurrentDate = [@Date]
var CurrentMonth = month( CurrentDate )
var CurrentDay = day( CurrentDate )
var Bucket =
switch( true(),
15 < CurrentDay, CurrentMonth,
CurrentMonth - 1
)
return
Bucket
),
"Date", [@Date],
"Bucket",
"Month_" & format(
if( [@Bucket] = 0, 12, [@Bucket] ),
"00"
)
)``````
Solution Sage

Power Query's probably the best place for this:

= Table.AddColumn(#"Previous Step", "Categorisation Month", each if [Day]> 15 then [Month Name] else Date.MonthName(Date.AddMonths([Date], -1)), type text)

Solution Sage

What does it mean manually? If you have a table with dates, then it's easy to write a formula that will do the categorisation automatically. Since you know that MonthN (Month01, Month02,..., Month12) starts on the 16th and ends on the 15th of the next month it does not take a long time to actually come up with a formula...

Frequent Visitor

If I knew how to make the formula, I wouldn't be on this page asking for support. Can you help me? Please?

Solution Sage

Another variation with month names is this:

``````EVALUATE
var Dates = CALENDAR( "2020-01-01", "2021-12-31" )
return
Dates,
"Bucket",
var CurrentDate = [Date]
var CurrentMonth = month( CurrentDate )
var CurrentDay = day( CurrentDate )
var BucketMonthNumber =
switch( true(),
15 < CurrentDay, CurrentMonth,
if( CurrentMonth - 1 = 0,
12,
CurrentMonth - 1
)
)
var Bucket =
format(
date( 2020, BucketMonthNumber, 1 ),
"MMM"
)
return
Bucket
)``````
Solution Sage

I'd suggest having a date table in your model that includes a column that categorises dates into your required buckets.

eg

 Date Calendar Month Categorisation Month 13-Feb-21 February January 14-Feb-21 February January 15-Feb-21 February January 16-Feb-21 February February 17-Feb-21 February February 18-Feb-21 February February 19-Feb-21 February February

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors