DAX to return current month

[samblackshaw28]

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:

Revenue add MTD =

CALCULATE(

    [Pipeline],

    'Calendar-Lookup'[Start of Month - Text]="01/08/2024").

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.

[suparnababu8]

Hi @samblackshaw28 

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!

[jdbuchanan71]

@suparnababu8 

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/

[jdbuchanan71 · Accepted solution]

@samblackshaw28 

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 )