How to calculate total sales as of first day of the current month as Previous month sales in power BI

Question

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.

Answer 1

If I understand your question correctly, you can use the following DAX measure:

Total Sales = 
var currentDate = MAX(myTable[saleDate])
var firstOfMonth = DATE(YEAR(CALCULATE(MAX(myTable[saledate]), ALL(myTable))),
    MONTH(CALCULATE(MAX(myTable[saledate]), ALL(myTable))), 1)
var result = SUM(myTable[salesamount])

Return IF(currentDate < firstOfMonth, result)

This will take the current date of the report context and compare it to the 1st of the current month. If the currentDate is less than the 1st of the current month, the result will be calculated. If your dataset has a Date table, you can replace the 'myTable[saledate]' references with a reference to the Date column in your date table.

With some sample data, here are the results:

(I added the firstOfMonth column for demonstration purposes)