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
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 :
Solved! Go to Solution.
@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 ),
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you, Adam.
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
@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 ),
⭕ 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
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you, Fowmy.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
14 | |
12 | |
10 | |
10 |