Forum Discussion
DAX to return current month
Hi,
This is probably a really simple question but I'm new to DAX and even after trying to use AI to produce the code for me, it's still not working.
Essentially, I want to be able to use the calculate function and make it only return data for the current month. At the minute, i've got a calender lookup table with a 'Start of month' column and i've duplicated that column and turned it into text, so I can use this:
I want to change this so its not hard-coded, and just returns values in the current month. For some reason I can't figure out how to do it. 'Pipeline' is the a measure which calculates income and is a seperate table to the calender lookup. There is a relationship between these 2 tables.
GIve this a try, not sure if I named the actual start of month column in your date table correctly.
Revenue add MTD = VAR _StartOfMonth = EOMONTH ( TODAY (), -1 ) + 1 //find the end of last month then add a day to get the start of this month RETURN CALCULATE ( [Pipeline], 'Calendar-Lookup'[Start of Month] = _StartOfMonth )
3 Replies
GIve this a try, not sure if I named the actual start of month column in your date table correctly.
Revenue add MTD = VAR _StartOfMonth = EOMONTH ( TODAY (), -1 ) + 1 //find the end of last month then add a day to get the start of this month RETURN CALCULATE ( [Pipeline], 'Calendar-Lookup'[Start of Month] = _StartOfMonth )Try with this measure
RevenueAddMTD = CALCULATE([Pipeline], FILTER('Calendar-Lookup', MONTH('Calendar-Lookup'[Start of Month]) = MONTH(TODAY()) && YEAR('Calendar-Lookup'[Start of Month]) = YEAR(TODAY())) )Hope this helps you!
You want to avoid using FILTER over an entire table. The performance hit can be substantial.
https://www.sqlbi.com/articles/filter-columns-not-tables-in-dax/
