CODEWITHSUNDEEP
powerbidax
ekan
ekan

Reputation: 55

DAX formula for previous month's values

I'm new to DAX. In order to get the previous month's sales, I have used DATESBETWEEN and passed MAX(Date)-1 as the start date and as the end date EOMONTH.

I got the correct results. However, what are the other simpler ways of getting the same?

CALCULATE (
SUM ( AW_Sales[Sales Amount] ),
DATESBETWEEN (
    AW_Calender_Lookup[Date],
    DATE ( YEAR ( MAX ( AW_Calender_Lookup[Date] ) ), MONTH ( MAX ( AW_Calender_Lookup[Date] ) ) - 1, 01 ),
    EOMONTH (
        DATE ( YEAR ( MAX ( AW_Calender_Lookup[Date] ) ), MONTH ( MAX ( AW_Calender_Lookup[Date] ) ) - 1, 01 ),
        0
    )
)

)

update - based on an answer given I added a new measure, but the value is empty. As you can see Number 1 from the bottom detailed matrix is placed on the summary matrix at the top, so as number 2, but number 3 (done using previous month) doesn't produce a total and empty in summary matrix

Upvotes: 0

Views: 13077

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

If you have a Dates table which is basically a calendar table, there is a simple way. Your data/fact table has to be connected to the Dates table using the date column for say date. Now you can create your Date slicer from the Dates table. For say, you selected the month September-2020 from the slicer, this below measure will return the SUM for current month-

total_sales = SUM(table_name[sales])

And this below measure will return the total sales for previous month-

total_sales_previous_month = 
CALCULATE(
    [total_sales], 
    PREVIOUSMONTH('Dates'[date])
)

For more information on DAX function PREVIOUSMONTH, you can check this.

Upvotes: 1

Related Questions