Using current trend to forecast sales

DAX DAX Calculations
2

This is a really interesting scenario you’re looking to solve.

I would do this slightly differently.

First I would create a calculated column which works out which day of the quarter each date is.

You can do that with this formula

Quarter Day = ( Dates[Date] - STARTOFQUARTER( Dates[Date] ) ) + 1

Then I would create a measure that used this logic.

Quarterly Forecast = 
CALCULATE( [Total Sales],
    FILTER( ALL( Dates ),
        Dates[Year] = 2015 &&
        Dates[QuarterOfYear] = 3 &&
        Dates[Quarter Day] = MIN( Dates[Quarter Day] ) ) )

You see I’ve hard coded the year and quarter. You would need to customize this somehow.

You would just sub in you current calculation where I have total sales also.

This patter will just iterate through the same number every single quarter continuously.

image
image.png1188×546 45.3 KB

See how you go with these techniques.

Chrs