Skip to main content
cancel
Showing results for 
Search instead 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

Reply
descud
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

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

View solution in original post

7 REPLIES 7
descud
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?

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

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)

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

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

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
Solution Sage
Solution Sage

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

eg

DateCalendar MonthCategorisation Month
13-Feb-21FebruaryJanuary
14-Feb-21FebruaryJanuary
15-Feb-21FebruaryJanuary
16-Feb-21FebruaryFebruary
17-Feb-21FebruaryFebruary
18-Feb-21FebruaryFebruary
19-Feb-21FebruaryFebruary

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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