Forum Discussion

VP_VishnuVS3's avatar
VP_VishnuVS3
Icon for Helper I rankHelper I
4 years ago

Forecast in DAX

Hi All,   I have a scenario which requeried rolling forcast for the next three years. Please see the sample data below   Jan-22 34.69 Feb-22 86.72 Mar-22 64.75 Apr-22 52.9 May-...
DAX
POWER BI Dax rules
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi VP_VishnuVS3 

you may try

Rolling Forcast Average =
VAR CurrentDate =
    MAX ( 'Date'[Date] )
VAR CurrentYear =
    YEAR ( CurrentDate )
VAR LastDateWithSales =
    CALCULATE ( Sales[Date], REMOVEFILTERS () )
VAR NumberOfDays =
    CALCULATE ( COUNTROWS ( 'Date' ), REMOVEFILTERS (), 'Date'[Year] = CurrentYear )
VAR LastDate = CurrentDate + NumberOfDays
VAR Result =
    IF (
        CurrentDate <= LastDateWithSales - 365,
        CALCULATE (
            [Sales Amount],
            REMOVEFILTERS ( 'Date' ),
            'Date'[Date] <= LastDate,
            'Date'[Date] > CurrentDate
        )
    )
RETURN
    Result
VP_VishnuVS3's avatar
VP_VishnuVS3
Icon for Helper I rankHelper I
4 years ago

Thank You!! But the rolling forecast is not happening with this query.