Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
VP_VishnuVS3
Helper I
Helper I

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-2234.69
Feb-2286.72
Mar-2264.75
Apr-2252.9
May-2245.37
Jun-2245.9
Jul-2247.27
Aug-2244.83
Sep-2244.86
Oct-2243.5
Nov-2240.49
Dec-2237.69

 

Logic for forecasting is for Jan 23, Average of (Jan 22 to Dec 22), Feb 23 is Average of (Feb 22 to Jan 23), Mar 23 is Average of (March 22 to Feb 23) like this upto Dec 25 we need to iterate. 

 

I got a solution in Power Query. But it would be great if there any Dax solution for this. 

 

 

Thanks in Advance,

VP

3 REPLIES 3
daXtreme
Solution Sage
Solution Sage

Finding a non-recursive formula for this problem, even though possible, will be very, very tedious. Sadly, you can't use recursion (which would make this very easy) because DAX does not support recursion 😞

tamerj1
Super User
Super User

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors