topic Re: Divide months from day 16 to 15 in DAX Commands and Tips

Divide months from day 16 to 15

Anonymous — Fri, 09 Jul 2021 14:05:13 GMT

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.

Re: Divide months from day 16 to 15

PaulOlding — Fri, 09 Jul 2021 16:05:39 GMT

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

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

Re: Divide months from day 16 to 15

Anonymous — Fri, 09 Jul 2021 17:41:12 GMT

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?

Re: Divide months from day 16 to 15

daxer-almighty — Fri, 09 Jul 2021 19:36:49 GMT

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

Re: Divide months from day 16 to 15

Anonymous — Fri, 09 Jul 2021 19:54:14 GMT

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

Re: Divide months from day 16 to 15

PaulOlding — Fri, 09 Jul 2021 20:07:17 GMT

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)

Re: Divide months from day 16 to 15

daxer-almighty — Fri, 09 Jul 2021 20:16:08 GMT

Drop this into DAX Studio and execute:

EVALUATE var Dates = SELECTCOLUMNS( CALENDAR( "2020-01-01", "2021-12-31" ), "@Date", [Date] ) return SELECTCOLUMNS( ADDCOLUMNS( 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" ) )

Re: Divide months from day 16 to 15

daxer-almighty — Fri, 09 Jul 2021 20:31:07 GMT

Another variation with month names is this:

EVALUATE var Dates = CALENDAR( "2020-01-01", "2021-12-31" ) return ADDCOLUMNS( 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 )