Calculate past 2 orders for each month

[ScottWalter]

I want to show the last 2 months orders on each row/month like last month orders is showing on each row. 

I have tried using that dax logic below

OrdersPrev2Month = sumx(FILTER(AW_Sales, AW_Sales[OrderDate] >= STARTOFMONTH(AW_Sales[OrderDate])-2 &&

AW_Sales[OrderDate] <= AW_Sales[OrderDate]), [Total Orders])

but it not showing the correct result. Can anyone please help me about building this logic.

[amitchandak · Accepted solution]

@ScottWalter , You can try measure like

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

2nd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))

Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

2 Months behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Month))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure whether I understood your question correctly, but please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

Orders total: = 
SUM( Data[Orders] )

Orders prev month: = 
CALCULATE ( [Orders total:], DATEADD ( 'Calendar'[Date], -1, MONTH ) )

Orders prev two months: = 
CALCULATE (
    [Orders total:],
    DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -2, MONTH )
)

[tamerj1]

@ScottWalter 

Relying on month level shall be dimple and more accurate than working on day level but you have to gave a year-month number column in your date table.  Do you have it?

[amitchandak · Accepted solution]

@ScottWalter , You can try measure like

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

2nd last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))

Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

2 Months behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-2,Month))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...