topic Re: help with DAX MTD in DAX Commands and Tips

help with DAX MTD

Midway — Tue, 18 Mar 2025 21:18:59 GMT

Hi there,

I'm really struggling with this calculation with this DAX measure; What I need to accomplish is show let's sales numbers in a card. When a month is selected in another visual (like a timeline), the card should show the "sales" numbers for the selected month and that is working perfectly, what is not working is when no selection, the card should default to show "sales" for the current month, and that's is not working at all.

This is DAX I'm using right now;

CALCULATE(

SUM(Fact[SALES]),

DATESMTD(DimDate[Date])

)

Any help would be highly appreciated

Re: help with DAX MTD

Deku — Tue, 18 Mar 2025 21:36:26 GMT

var dt = max( DimDate[Date] ) return CALCULATE( SUM(Fact[SALES]), KEEPFILTERS( DATESBETWEEN( DimDate[Date], EOMONTH( dt, -1) + 1, dt ), dt ) ) )

Re: help with DAX MTD

Midway — Tue, 18 Mar 2025 22:51:31 GMT

hey there, thanks for replying to my post. I tried this formula but it's giving an error, "unexpected parameter , dt". If I remove runs fine but still does not work. Still no sales without selecting any data points.

Re: help with DAX MTD

Deku — Tue, 18 Mar 2025 22:52:33 GMT

Where is it complaining?

Re: help with DAX MTD

Deku — Tue, 18 Mar 2025 23:05:35 GMT

Updated

var dt = MAX( DimDate[Date] ) return CALCULATE( SUM(Fact[SALES]), DATESBETWEEN( DimDate[Date], EOMONTH( dt, -1) + 1, dt ) )

Re: help with DAX MTD

Midway — Tue, 18 Mar 2025 23:38:15 GMT

it works when selecting any month but it's not defaulting to show current month sales when there is no selection.

Re: help with DAX MTD

Pow3Range — Wed, 19 Mar 2025 03:53:38 GMT

UPDATED
-------
VAR DATE_ =
IF(
ISFILTERED(DimDate[Date]),
MAX(DimDate[Date]),
TODAY()
)
RETURN
CALCULATE(
SUM(Fact[SALES]),
DATESBETWEEN(DimDate[Date], EOMONTH(DATE_, -1) + 1, EOMONTH(DATE_, 0))
)

Re: help with DAX MTD

techies — Wed, 19 Mar 2025 05:07:31 GMT

Hi Midway please check this

ResultMeasure=

VAR IsMonthSelected = ISFILTERED('DATE'[Month])

VAR DefaultMonthSales =
CALCULATE(
SUM('sheet1'[enrollment amount]),
FILTER(
ALL('DATE'),
'DATE'[Year] = YEAR(TODAY()) && 'DATE'[Month] = MONTH(TODAY())
)
)

VAR SelectedMonthSales =
CALCULATE(
SUM('sheet1'[enrollment amount]),
DATESMTD('DATE'[Date])
)

RETURN
IF(
IsMonthSelected,
SelectedMonthSales,
DefaultMonthSales
)

Re: help with DAX MTD

Midway — Wed, 19 Mar 2025 15:02:15 GMT

Thanks for replying to my post, this gets the default sales numbers for the current month when no selection has been made, but it keeps the same current month sales numbers when selecting other months.

Re: help with DAX MTD

Midway — Wed, 19 Mar 2025 15:02:31 GMT

Re: help with DAX MTD

Deku — Wed, 19 Mar 2025 15:10:17 GMT

var dt = eomonth( MAXX( summarize( fact, DimDate[Date] ), dimdate[date]), 0)

return

CALCULATE(

SUM(Fact[SALES]),

DATESBETWEEN( DimDate[Date], EOMONTH( dt, -1) + 1, dt )

)

Re: help with DAX MTD

bhanu_gautam — Wed, 19 Mar 2025 15:56:43 GMT

Midway Use DAX
SalesMeasure :=
IF(
ISFILTERED(DimDate[Month]),
CALCULATE(
SUM(Fact[SALES])
),
CALCULATE(
SUM(Fact[SALES]),
DATESMTD(DimDate[Date])
)
)

Re: help with DAX MTD

Midway — Wed, 19 Mar 2025 16:05:43 GMT

Thansk very much Deku!! this did the trick!