Getting previous 3 months sales based on MTD Sales column

[smko]

Greetings people, I'm trying to get previous 3 months sales amount in current row, however due to the raw data is in Month-To-Date structure, MTD sales column will always reset to 0 at the beginning of the month, I find it hard find the correct solution. Here is how the raw data looks like, do note the date will not end at last day of the month.

Date	MTD Sales	Excepted Result
29/06/2021	100
30/07/2021	120	100	=0+0+100
30/08/2021	200	220	=0+100+120
05/09/2021	50	420	=100+120+200
12/09/2021	70	420	=100+120+200
29/09/2021	300	420	=100+120+200
03/10/2021	30	620	=120+200+300

I tried the code but it will sum up all sales if there are multiple dates in a month. My idea is to get the last non blank date of each 3 previous month then maybe tweak the logic in my code. Appreciate anyone can provide better code and logic

Last3monthsSales = 
VAR Prev3months = STARTOFMONTH(DATEADD('Calendar'[Date],-3,MONTH))
VAR SOmonth = STARTOFMONTH('Calendar'[Date])
VAR Result = 
    CALCULATE(
        SUM(Sales[MTD Sales]),
        FILTER(
            ALL('Calendar'[Date]),
            'Calendar'[Date] < SOmonth && 'Calendar'[Date] >= Prev3months
        )
    )
RETURN Result

[V-lianl-msft · Accepted solution]

Please try to create a new column to calculate sales on the last date of each month.

last_sales = 
var max_date = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[year_month]))
return IF('Table'[Date]=max_date,'Table'[ MTD Sales ])

[amitchandak]

@smko , Takes sales base measure

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),0) ,-3,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.

[smko]

Hi there @amitchandak , there is no Sales Amount column, only has MTD Sales column