Backlog forecast allocation DAX formula

DAX DAX Calculations
1

Hi everyone,

I have been trying multiple techniques used in the Budgeting Allocation videos and searching topics across the forum for many days but I can’t find a way to solve my problem. Maybe the solution is easier than what I tried to replicate.
Rolling backlog Forecast test.pbix (1.6 MB)

What I want is to calculate the forecasted revenue based on the remaining contract backlog over a period of time dynamically.

I want to distribute (proportionnaly over the ending date of the project) the remaining backlog preferably by months over next 12 months for example or next 24 months.

Remark: the remaining backlog amount is not fixed, every month it diminishes, so I need to recalculate month over month.

I have created a fake sample with only 2 projects in the pbix file.

Many thanks for your help.

Regards

2

Hello @Rachid,

Thank You for posting your query onto the Forum.

In order to apportion the backlog amount over the ending date of the project. Below is the DAX measure alongwith the screenshot of the final results provided for the reference -

Total Forecast = 

-- Apportionment of Backlog Amount From 01/01/2023 Till Ending Dates of the respective Projects


VAR _Backlog_As_On = 
DATE( YEAR( TODAY() ) , MONTH( TODAY() ) , 01 )

VAR _vTable_Forecasts =
ADDCOLUMNS(
    SUMMARIZE(
        Projects , 
        Projects[Project Code] ,
        Projects[Ending Date] ) , 
    "@Total_Forecast" , 
    DIVIDE(
        CALCULATE( SUM( Projects[Backlog Amount as of 01/01/2023] ) ) , 
        DATEDIFF( _Backlog_As_On , Projects[Ending Date] , DAY ) + 1 ) )

VAR _Filtered_Table = 
FILTER(
    CROSSJOIN(
        VALUES( Dates[Date] ) ,
        _vTable_Forecasts ) , 
    Dates[Date] >= _Backlog_As_On &&
    Dates[Date] <= CALCULATE( MAX( Projects[Ending Date] ) ) )

VAR _Results = 
SUMX(
    _Filtered_Table , 
    [@Total_Forecast] )

RETURN
_Results

Final Results
Final Results1706×959 77.9 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Rolling Backlog Forecast - Harsh.pbix (1.6 MB)

3

wow brilliant ! thank you so much for the quick reply !

Do you know which course on the website is covering this calculation technique ?

Thanks again

4

Hello @Rachid,

You’re Welcome!

I’m glad that I was able to assist you.

This technique is a consolidation of various concepts coming together so I’m not sure it’s covered in any of our previous videos as of now. Just few days back I covered and explained this technique to other forum member. Below is the link of that post provided for the reference.

Thanks and Warm Regards,
Harsh

1 Like