Divide months from day 16 to 15

[descud]

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.

[descud]

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?

[daxer-almighty · Accepted solution]

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"
        )
)

[PaulOlding]

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)

[daxer-almighty]

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

[descud]

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

[daxer-almighty]

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
    )

[PaulOlding]

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