topic Re: Previous Month DAX Calculation in DAX Commands and Tips

Previous Month DAX Calculation

bmk — Tue, 23 Jan 2024 04:11:14 GMT

I seem to be running into a road block and I think this may be straightforward. If someone can please help.

I am using a table visual on PBI, with a bunch of measures. I have a date table. I wish to calculate a DAX filter formula on the date table to give me dates of the previous month relative to today. Assuming today's date is January 2024, I want all calculations and measures only from Dec 2023. The date table i'm using is :

Date Table =

ADDCOLUMNS (

CALENDAR (

DATE ( YEAR ( today() -9999), MONTH ( today() - 9999), DAY ( today()- 9999) ),

DATE ( YEAR ( today() ), MONTH ( today() ), DAY ( today() ) )

"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),

"Year", YEAR ( [Date] ),

"Monthnumber", FORMAT ( [Date], "MM" ),

"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),

"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),

"MonthNameShort", FORMAT ( [Date], "mmm" ),

"MonthNameLong", FORMAT ( [Date], "mmmm" ),

"DayOfWeekNumber", WEEKDAY ( [Date] ),

"DayOfWeek", FORMAT ( [Date], "dddd" ),

"DayOfWeekShort", FORMAT ( [Date], "ddd" ),

"Quarter", "Q" & FORMAT ( [Date], "Q" ),

"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q"

& FORMAT ( [Date], "Q" ),

"Start of Week",[Date] - WEEKDAY([Date],2) +1,

"Week Ending",[Date] + 7 - WEEKDAY([DATE],2),

"Week Range",[Date] - WEEKDAY([Date],2) +1&" - "&[Date] + 7 - WEEKDAY([DATE],2),

"MonthYear", FORMAT ( [Date], "mmmm" )&"/"&YEAR ( [Date] ),

"WeekNum",weeknum([Date],2)-1

)

and the DAX I tried is :

IsPreviousMonth =

IF(

'Date Table'[Date] >= STARTOFMONTH('Date Table'[TodayDate])

&& 'Date Table'[Date] < STARTOFMONTH('Date Table'[TodayDate]) - 1,

)

Re: Previous Month DAX Calculation

talespin — Tue, 23 Jan 2024 05:39:50 GMT

Hello,

This returns a table with all dates(datetime) from previous month. Please replace first parameter with your calendar table.

DATESBETWEEN( 'Calendar'[MasterDate], EOMONTH(TODAY(), -2) + 1 , EOMONTH(TODAY(), -1))

Re: Previous Month DAX Calculation

Fowmy — Tue, 23 Jan 2024 07:23:59 GMT

bmk

Add calculated column as follows:

Previous Month = INT('Date Table'[Date] IN CALENDAR( EOMONTH( TODAY() , -2)+1 , EOMONTH( TODAY() , -1) ))

I suggest you modify your calender table date range as follows, not to have too many dates/rows. I entere 10 years back so you can chagne it in terms of years:

ADDCOLUMNS (
CALENDAR (
DATE ( YEAR ( today() ) - 10 ) ,1 ,1 ),
DATE ( YEAR ( today() ),12 ,31 ),

Re: Previous Month DAX Calculation

bmk — Tue, 23 Jan 2024 14:11:48 GMT

Fowmy, the calculated column worked great, thank you. I did try to modify the Date DAX overall as per your suggestion, but I am getting a DAX syntax error. Can you please check what I am missing.

Date Table =
ADDCOLUMNS (
CALENDAR (
DATE ( YEAR ( today() ) - 10 ) ,1 ,1 ),
DATE ( YEAR ( today() ),12 ,31 ),
),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q"
& FORMAT ( [Date], "Q" ),
"Start of Week",[Date] - WEEKDAY([Date],2) +1,
"Week Ending",[Date] + 7 - WEEKDAY([DATE],2),
"Week Range",[Date] - WEEKDAY([Date],2) +1&" - "&[Date] + 7 - WEEKDAY([DATE],2),
"MonthYear", FORMAT ( [Date], "mmmm" )&"/"&YEAR ( [Date] ),
"WeekNum",weeknum([Date],2)-1
)

Re: Previous Month DAX Calculation

Anonymous — Wed, 24 Jan 2024 09:43:43 GMT

Hi bmk ,

I've changed your code., you can try below dax:

Date Table = ADDCOLUMNS ( CALENDAR ( DATE ( YEAR ( today() ) - 10 ,1 ,1 ), DATE ( YEAR ( today() ),12 ,31 )), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "DayOfWeekNumber", WEEKDAY ( [Date] ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "DayOfWeekShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ), "Start of Week",[Date] - WEEKDAY([Date],2) +1, "Week Ending",[Date] + 7 - WEEKDAY([DATE],2), "Week Range",[Date] - WEEKDAY([Date],2) +1&" - "&[Date] + 7 - WEEKDAY([DATE],2), "MonthYear", FORMAT ( [Date], "mmmm" )&"/"&YEAR ( [Date] ), "WeekNum",weeknum([Date],2)-1 )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Previous Month DAX Calculation

Fowmy — Wed, 24 Jan 2024 11:35:20 GMT

bmk

I fixed it, please try now.

Date Table = ADDCOLUMNS ( CALENDAR ( DATE ( YEAR ( today() ) - 10 ,1 ,1 ), DATE ( YEAR ( today() ), 12 ,31 ) ), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "DayOfWeekNumber", WEEKDAY ( [Date] ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "DayOfWeekShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ), "Start of Week",[Date] - WEEKDAY([Date],2) +1, "Week Ending",[Date] + 7 - WEEKDAY([DATE],2), "Week Range",[Date] - WEEKDAY([Date],2) +1&" - "&[Date] + 7 - WEEKDAY([DATE],2), "MonthYear", FORMAT ( [Date], "mmmm" )&"/"&YEAR ( [Date] ), "WeekNum",weeknum([Date],2)-1 )

Re: Previous Month DAX Calculation

bmk — Wed, 24 Jan 2024 14:37:01 GMT

Thank you, Adam.

Re: Previous Month DAX Calculation

bmk — Wed, 24 Jan 2024 14:37:50 GMT

Thank you, Fowmy.