topic Calculated DAX for Current Month in DAX Commands and Tips

Calculated DAX for Current Month

samblackshaw28 — Thu, 01 Aug 2024 11:31:50 GMT

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.

Re: Calculated DAX for Current Month

Gabry — Thu, 01 Aug 2024 11:58:12 GMT

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

Re: Calculated DAX for Current Month

sjoerdvn — Thu, 01 Aug 2024 12:00:56 GMT

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.

Re: Calculated DAX for Current Month

Anonymous — Mon, 05 Aug 2024 07:06:41 GMT

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.