How to calculate total sales between start and end of previous month in DAX?

[Anonymous]

I want to calculate the total sales between start of previous month (example - 1/2/2021) and end of previous month (example - 28/2/2021). Here is my attempt below:

Month Saving = IF(
    COUNTROWS(Savings)>0,
    CALCULATE(
            SUMX(Savings,Savings[Forecast Annual Cost Reduction - Use USD exchange rate of the day]),
            DATESBETWEEN('Dates'[Date],STARTOFMONTH(DATEADD('Dates'[Date],-1,MONTH)),ENDOFMONTH(DATEADD('Dates'[Date],-1,MONTH))
        ),
    BLANK ()
))

But this is giving me error and not working. Can anyone please help me on this?

[Anonymous · Accepted solution]

Hi,@Anonymous 

I think you can use another DAX to calculate the total sales of previous month .

First ,you should create a Calendar Date table as a slicer to filter the Sales for product table .

Previous month total sales = CALCULATE(SUM('Sales for product'[sales]),PREVIOUSMONTH('Date'[Date]))

The effect is as shown:

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Does this measure work?

Month Saving = IF(COUNTROWS(Savings)>0,CALCULATE(SUM(Savings[Forecast Annual Cost Reduction - Use USD exchange rate of the day]),PREVIOUSMONTH('Dates'[Date])))

[Anonymous]

Hi @Ashish_Mathur this is working but It populates values for all previous months of the date. What I want to do is to calculate the total sales in the previous month of current month and visualize it in Card or Guage visuals. 

[Ashish_Mathur]

Hi,

Try this measure

Month Saving = IF(COUNTROWS(Savings)>0,CALCULATE(SUM(Savings[Forecast Annual Cost Reduction - Use USD exchange rate of the day]),DATESBETWEEN('Dates'[Date],EOMONTH(TODAY(),-2)+1,EOMONTH(TODAY(),-1))))