Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"
)
)
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
)
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |