Forum Discussion

almudeve's avatar
almudeve
Frequent Visitor
2 years ago
Solved

Previous MTD Calculation

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, a...
  • almudeve's avatar
    almudeve
    2 years ago

    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!

     

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
2 years ago

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's avatar
almudeve
Frequent Visitor
2 years ago

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!