Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
bmk
Helper II
Helper II

Previous Month DAX Calculation

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,
        1,
        0
    )
1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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 ),




Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

7 REPLIES 7
bmk
Helper II
Helper II

Thank you, Adam.

v-kongfanf-msft
Community Support
Community Support

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
)

vkongfanfmsft_0-1706089396291.png

 

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

Fowmy
Super User
Super User

@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 ),




Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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
)

@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
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Thank you, Fowmy.

talespin
Solution Sage
Solution Sage

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors