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, 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!

  • 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!

     

2 Replies

  • 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!