topic Re: Previous MTD Calculation in DAX Commands and Tips

Previous MTD Calculation

almudeve — Mon, 11 Mar 2024 00:48:53 GMT

Dear community,

I have the following DAX for calculate the previous MTD sales and it is not working as I expected.

Today is 10th March, and I need the sales from 1st February to 10th February, and I am getting the whole month instead of the 10 first days.

Is the following DAX wrong? Do you have any idea why is not doing the calculate I'm looking for?

Sales_PreviousMTD =

Var _LastDate = LASTDATE(sales_table[date])

return

CALCULATE(

SUM(sales_table[sales_amount]),

DATEADD(

FILTER(DATESMTD(sales_table[date]),_LastDate),-1,MONTH)

)

Thanks!

FreemanZ — Mon, 11 Mar 2024 05:38:45 GMT

Not sure if i fully get you. Supposing you have a data table like:

1) try to add a calculated dates table like:

dates = ADDCOLUMNS( CALENDAR(MIN(data[Date]), MAX(data[Date])), "YY/MM", FORMAT([Date], "YY/MM") )

2) relate data[date] with dates[date]

3) plot a table visual with dates[yy/mm] with a measure like:

PreMTD = CALCULATE( SUM(data[Sales]), DATEADD(DATESMTD(dates[Date]), -1, MONTH), DAY(data[Date])<=DAY(TODAY()) )

it works like:

almudeve — Mon, 11 Mar 2024 16:32:46 GMT

You almost got it!!

I have resolved it and it is close to your solution, thank you very much!!

The solution is:

Sales_PreviousMTD =

Var _LastDate = LASTDATE(sales_table[date])

return

CALCULATE(

SUM(sales_table[sales_amount]),

DATESMTD(

DATEADD(_LastDate),-1,MONTH)

)

Hope this helps to anyone who is facing the same issue!