Forum Discussion

cyborgandy's avatar
cyborgandy
Helper II
3 years ago

Forecast 2 weeks trend

Hi All,

 

I have below data in my file, I need to write a DAX which can forcast the coverage value for next 2 weeks as per the existing last 8 weeks actual data, could someone help me with the DAX.

 

DateCoverage
7/18/202343314.28
7/19/202346587.21
7/21/202350430.32
7/24/202357365.00
7/25/202364887.25
7/26/202368135.19
7/27/202370492.60
7/28/202372082.89
7/31/202375085.72
8/2/202384301.20
8/7/202396150.63
8/8/202318234.13
8/9/202321217.45
8/10/202327157.69
8/11/202336911.57
8/14/202341654.31
8/15/202349327.01
8/16/202350051.84
8/17/202355220.42
8/18/202355220.42
Need Help

3 Replies

  • JoeBarry's avatar
    JoeBarry
    Solution Sage
    3 years ago

    Hi cyborgandy 

     

    Do you have a date table in your dataset with a relationship to this Fact table? Please try below

     

    Lets say the table above is called Coverage Table and you have created a measure called Coverage. There is no week available in Power BI, so we will multiple 7 days by 8 weeks to get 56 days to look back.

     

    Measure 1

     

    Avg. Coverage - Last 8 Weeks = 
VAR NumOfDays = 56
VAR LastCurrentDate =
    MAX ( 'DIM Date'[Date] )
VAR Period =
    DATESINPERIOD ( 'DIM Date'[Date], LastCurrentDate, - NumOfDays, DAY)
VAR Result =
    CALCULATE (
        AVERAGEX ( VALUES ( 'DIM Date'[Date] ), [Coverage] ),
        Period
    )
VAR FirstDateInPeriod =
    MINX ( Period, 'DIM Date'[Date] )
VAR LastDateWithSales =
    MAX ( 'CoverageTable'[Date] )
RETURN
    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

     

     

    Then to add the forecast for two weeks you will need to add this

     

     

    2 Weeks Forecast = CALCULATE([Avg. Coverage - Last 8 Weeks], DATEADD(DateTable[Date]), - 14, DAY))