How to calculate total sales as of first day of the current month as Previous month sales DAX

[unnipbi123]

I have salesamount column and the saledate column in my table. so I need to calculate total sales for each month based on below calculation.

total sales for current month=sum(salesamount) as of 1st day of the next month

for example sales of December-2021 should be calculated based on the total sales as on Jan-1-2022. total sales for January should be blank until Feb-1-2022 as it should be the total sales as on feb-1-2022. I am very much confused how we can achieve this in Dax. Really appreciate your help.

[v-yalanwu-msft]

Hi， @unnipbi123 ；

You could create a measure.

Measure = IF(MAX([Date])= STARTOFMONTH('Table'[Date]),
             CALCULATE(SUM([value]),FILTER(ALL('Table'),EOMONTH( [Date],0)=EOMONTH(MAX([Date]),-1))))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[unnipbi123]

@Tahreem24 

Thanks for the response. However with the below measure, i am getting data for January month as well since there are some sales for January. So its calculating total sales for January based on sales till date. But i want to see data for January only on Feb 1. Till then it should not calculate total sales for January. Please let me know if this can be acheived

[Tahreem24 · Accepted solution]

@unnipbi123 TRy this:

Measure =

VAR cal_ = CALCULATE(SUM('Table'[Sales]),DATEADD(STARTOFMONTH('Table'[Date]),1,MONTH))

RETURN IF(MONTH(MAX('Table'[Date]))<MONTH(TODAY()),BLANK(),cal_)

[Tahreem24]

@unnipbi123 Create a measure like below:

Measure = CALCULATE(SUM('Table'[Sales]),DATEADD(STARTOFMONTH('Table'[Date]),1,MONTH))