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
cottrera
Post Prodigy
Post Prodigy

DAX Forecasting help

 

Hi

A bit of a complicated DAX question so here goes.

 

I am trying to create a report that takes historical, and current information to try and predict some future scenarios.

The visual table below displays logged jobs and completed jobs by year / month. The Variance shows whether we have completed more or less than logged.

 

The WIP (work in progress) means how many jobs in total are outstanding at the beginning of each month.

 

We have come to realise that the Variance from the previous month positively or negatively affects the following months WIP. If I dived the Variance by 2 and subtract this result from the previous months WIP this gives me the forecasted WIP

I have written some DAX functions to try and forecast the future WIP (Jan- Dec 2022) but I can only get it to work for Jan 2022.

 

For example, if you look at December 2021, we logged 971 jobs and completed 857. The variance is -114. If I then divide this variance by 2 it gives me -57. I then subtract -57 from the WIP and this give me the forecast for the following month January 2022. You can see from the last 5 columns on the visual table this works. However, it only calculates it for Jan 22 and not the other months throughout 2022

 

cottrera_0-1639391544421.png

 

Hopefully my explanation make sense.

 

Richard

1 ACCEPTED SOLUTION
v-chenwuz-msft
Community Support
Community Support

Hi @cottrera ,

 

It is certain that there is a problem with your filter conditions.

First, dax only calculate with the data which already existing, WIP calculates depend on itself which not existing.

But this don't mean it impossible to forecast the WIP. We can easily find that

the next WIP = current WIP + sum( current Variance)/2 

after the next WIP = current WIP + sum( current Variance + the next Variance )/2

second after the next WIP  = current WIP + sum( current Variance + the next Variance + after the next Variance )/2

...

So we should construct a cache table to calculate.

Try the following code to create a measure:

Forecast WIP M =
VAR _firstrow =
    TOPN( 1, FILTER( ALL( 'Table' ), NOT ( ISBLANK( [WIP] ) ) ), [Month], DESC )
VAR _lastMonth =
    CALCULATE( MAX( 'Table'[Month] ), _firstrow )
VAR _lastWIP =
    CALCULATE( MAX( 'Table'[WIP] ), _firstrow )
VAR _sumVar =
    SUMX(
        FILTER(
            ALL( 'Table' ),
            [Month] >= _lastMonth
                && [Month] <= SELECTEDVALUE( 'Table'[Month] )
        ),
        [Variance]
    ) / 2
VAR _forecast = _lastWIP + _sumVar
RETURN
    IF( SUM( 'Table'[WIP] ) = BLANK(), _forecast, SUM( 'Table'[WIP] ) )

Result:

vchenwuzmsft_0-1639631411989.png

 

Pbix in the end you can refer.

 


Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

View solution in original post

5 REPLIES 5
cottrera
Post Prodigy
Post Prodigy

Hi chenwu zhu

 

Thank you again for your DAX function. It works fine on the static table your provided in your sample pbix file,

cottrera
Post Prodigy
Post Prodigy

Thank you.
I will try again to edit your code to suit my model. Failing this I will provide a pbix file once I have removed any sensitive data. 

Richard

cottrera
Post Prodigy
Post Prodigy

Hi Chenwu zhu

 

thank you for responding so quickly.  The table example I provided was a visual table. This is feed by my model that is made up of a number of other tables. Therefore I was unable to edit your dax function to suite my model. Below is an example of the model as you can see the WIP , forecast jobs,Logged jobs, completed jobs all come from seperate tables

cottrera_0-1639649727925.png

thank you

Richard

Hi @cottrera ,

 

I gave some calculation ideas because I saw that yours was provided as a visual screenshot.

It is indeed difficult to match your model. If you can reproduce this table in a new table, with the code above, it would be easy.

 

or provide pbix file without sensitive data.

 

Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-chenwuz-msft
Community Support
Community Support

Hi @cottrera ,

 

It is certain that there is a problem with your filter conditions.

First, dax only calculate with the data which already existing, WIP calculates depend on itself which not existing.

But this don't mean it impossible to forecast the WIP. We can easily find that

the next WIP = current WIP + sum( current Variance)/2 

after the next WIP = current WIP + sum( current Variance + the next Variance )/2

second after the next WIP  = current WIP + sum( current Variance + the next Variance + after the next Variance )/2

...

So we should construct a cache table to calculate.

Try the following code to create a measure:

Forecast WIP M =
VAR _firstrow =
    TOPN( 1, FILTER( ALL( 'Table' ), NOT ( ISBLANK( [WIP] ) ) ), [Month], DESC )
VAR _lastMonth =
    CALCULATE( MAX( 'Table'[Month] ), _firstrow )
VAR _lastWIP =
    CALCULATE( MAX( 'Table'[WIP] ), _firstrow )
VAR _sumVar =
    SUMX(
        FILTER(
            ALL( 'Table' ),
            [Month] >= _lastMonth
                && [Month] <= SELECTEDVALUE( 'Table'[Month] )
        ),
        [Variance]
    ) / 2
VAR _forecast = _lastWIP + _sumVar
RETURN
    IF( SUM( 'Table'[WIP] ) = BLANK(), _forecast, SUM( 'Table'[WIP] ) )

Result:

vchenwuzmsft_0-1639631411989.png

 

Pbix in the end you can refer.

 


Best Regards

Community Support Team _ chenwu zhu

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

 

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.