Calculated DAX for Current Month

[samblackshaw28]

Hi All,

I was wondering if there was a way to use the calculate function to calculate for current month. I know you can create a calculated column in the data set for this but i'd rather not add a whole column for just 'current month' true/false.

What i'm trying to do is calculate the income but only for the current month. In my head I thought I could do something like:

Calculate(
     [Income],
     ('Report'[Income Date],Month(Today))
)

But this doesn't work.
I also know I can just add a filter on the visuals but I'm going to reuse this measure so would rather it was consistent everywhere.

Does anyone know what the correct DAX is for this and if it's even possible?

Thankyou for your help.

  

[v-nuoc-msft · Accepted solution]

Hi @samblackshaw28 

@sjoerdvn @Gabry Thank you very much for your prompt reply, please allow me to share some content here.

For your question, here is the method I provided:

Here's some dummy data

“Report”

Create a measure.

Measure = 
var _today= TODAY()
RETURN
CALCULATE(
    SUM('Report'[Income]),
    FILTER(
        ALL('Report'),
        MONTH('Report'[Income Date]) = MONTH(_today) 
        &&
        YEAR('Report'[Income Date]) = YEAR(_today)))
        

Here is the result.

Regards,

Nono Chen

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

[sjoerdvn]

Instead of using DAX, you could also simply filter your visual or page. For date fields you will see relative date filter options.

What I tend to do however is to have a date dimension with a calculated "relative month" column, where 0 is the current and -1 the previous, so you can easily create filters and measures for current mont, previous month, last 12 months etc.

[Gabry]

Hello,

it depends on what you really want to do and how it is built your calendar table.

That said, you can use the function totalmtd.

Or for example if you have year and month columns in the calendar table you could write like this

calculate(income, calendar[month]=month(today()), calendar[year]=year(today()))

Hope this help you