Previous MTD Calculation

[almudeve]

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]

hi @almudeve ,

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

Date	Sales
1/1/2023	1
1/8/2023	1
1/15/2023	1
1/22/2023	1
1/29/2023	1
2/5/2023	1
2/12/2023	1
2/19/2023	1
2/26/2023	1
3/5/2023	1
3/12/2023	1

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 · Accepted solution]

Hi @FreemanZ ,

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!