Forecast Distribution

[AllanBerces]

Hi Good day,

Can anyone pls need help, how to make it in calculated column. I have a measure for Remaing hrs (Measure1), average earned hrs (Measure2) and Table with daily plan. I want to divide the remaining hrs by my average earned hrs and the result will distribute daily up to the day that the sum of my forecast equal to my remaining hrs. the catch now is.

IF my  daily plan is greater than the average earned hrs will just copy my daily plan
IF my daily plan is less than the everage hrs will reflect the average earned hrs.

Let say on table below my Remaning hrs is 187hrs and my average earned hrs is 25. On the output i can finish my work earlier than my plan.

Thank you in advance

[Daniel29195 · Accepted solution]

@AllanBerces 

if you dont want to create all these columns 

use this : 

combined = 

var ds = 
ADDCOLUMNS(
ADDCOLUMNS(
    ADDCOLUMNS(
        SUMMARIZE(
            'Table',
            'Table'[date],
            'Table'[planned]
        ),
        "forecasted", 
            var planned = 'Table'[planned]
            var earned = 25  --  dynamic base on your calculated measure i assume. 
            var forecasted = 
                    SWITCH(
                        TRUE(),
                        'Table'[date]< TODAY() , 0 , 
                        'Table'[planned] >= earned ,  planned,
                        'Table'[planned] < earned , earned 
                    )
            return forecasted,
        "remaining",
            SUMX(
                FILTER(
                    'Table',
                    'Table'[date]>= TODAY()
                ),
                'Table'[planned]
            )
    ),
    "cumul_foreacsted" , 
        SUMX(
            FILTER(
                'Table',
                'Table'[date] <=EARLIER('Table'[date])
            ),
            'Table'[forecasted]
        )
),
"forecasted corrected",
    var next_nb = 
    SELECTCOLUMNS(
            OFFSET(-1, 
                SUMMARIZE(
                    'Table',
                    'Table'[date],
                    'Table'[remaining],
                    'Table'[cumul forecasted]
                ),
                ORDERBY('Table'[date], asc )
            ),
            "next_period" , 'Table'[cumul forecasted]
    )


    var res = 
    SWITCH(
        TRUE(),
        'Table'[cumul forecasted] <= 'Table'[remaining]  ,   'Table'[cumul forecasted] - next_nb,
        abs('Table'[remaining] - 'Table'[cumul forecasted]) < 'Table'[earned] , 'Table'[remaining] -  next_nb
        )
        return res
)



return 
MAXX(
    FILTER(
        ds,
        'Table'[date] = EARLIER('Table'[date])
    ),
    [forecasted corrected]
)

[AllanBerces]

Hi Good day,

If i have multiple subcategory in one table sa below sample. how can i change the below solutions. Daily Plan, Average Earned, Remaining Hrs are given on my table.

Thank you to @Daniel29195 , @v-xuxinyi-msft for providing the solutions below

Thank you

[v-xuxinyi-msft · Accepted solution]

Hi @AllanBerces 

Your solution is great, @Daniel29195 . It worked like a charm! Here I have another idea in mind, and I would like to share it for reference.

Calculated column:

Column = 
VAR _RemainingHrs = 187  -- Replace [Measure1] with your actual measure for Remaining hrs
VAR _AvgEarnedHrs = 25  -- Replace [Measure2] with your actual measure for Average earned hrs
VAR _maxdate = [Date]
RETURN
IF([Daily Plan_rev] <> 0, IF([Daily Plan_rev] < _AvgEarnedHrs , _AvgEarnedHrs, [Daily Plan_rev]), 0)

Measure:

Forecast Distribution = 
VAR _RemainingHrs = 187  -- Replace [Measure1] with your actual measure for Remaining hrs
VAR _AvgEarnedHrs = 25  -- Replace [Measure2] with your actual measure for Average earned hrs
VAR _maxdate = MAX([Date])
VAR _sum = CALCULATE(SUM('Table'[Column]), FILTER(ALL('Table'), [Date] <= _maxdate))
VAR _result = IF(MAX([Daily Plan_rev]) <> 0, IF(MAX([Daily Plan_rev]) < _AvgEarnedHrs , _AvgEarnedHrs, MAX([Daily Plan_rev])), 0)
VAR _result1 = IF(_RemainingHrs - _sum >= _result, _result, _RemainingHrs - _sum)
RETURN
IF(_result1 < 0, BLANK(), _result1)

Result:

Best Regards,
Yulia Xu

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

[Daniel29195 · Accepted solution]

@AllanBerces 

if you dont want to create all these columns 

use this : 

combined = 

var ds = 
ADDCOLUMNS(
ADDCOLUMNS(
    ADDCOLUMNS(
        SUMMARIZE(
            'Table',
            'Table'[date],
            'Table'[planned]
        ),
        "forecasted", 
            var planned = 'Table'[planned]
            var earned = 25  --  dynamic base on your calculated measure i assume. 
            var forecasted = 
                    SWITCH(
                        TRUE(),
                        'Table'[date]< TODAY() , 0 , 
                        'Table'[planned] >= earned ,  planned,
                        'Table'[planned] < earned , earned 
                    )
            return forecasted,
        "remaining",
            SUMX(
                FILTER(
                    'Table',
                    'Table'[date]>= TODAY()
                ),
                'Table'[planned]
            )
    ),
    "cumul_foreacsted" , 
        SUMX(
            FILTER(
                'Table',
                'Table'[date] <=EARLIER('Table'[date])
            ),
            'Table'[forecasted]
        )
),
"forecasted corrected",
    var next_nb = 
    SELECTCOLUMNS(
            OFFSET(-1, 
                SUMMARIZE(
                    'Table',
                    'Table'[date],
                    'Table'[remaining],
                    'Table'[cumul forecasted]
                ),
                ORDERBY('Table'[date], asc )
            ),
            "next_period" , 'Table'[cumul forecasted]
    )


    var res = 
    SWITCH(
        TRUE(),
        'Table'[cumul forecasted] <= 'Table'[remaining]  ,   'Table'[cumul forecasted] - next_nb,
        abs('Table'[remaining] - 'Table'[cumul forecasted]) < 'Table'[earned] , 'Table'[remaining] -  next_nb
        )
        return res
)



return 
MAXX(
    FILTER(
        ds,
        'Table'[date] = EARLIER('Table'[date])
    ),
    [forecasted corrected]
)

[AllanBerces]

Hi @Daniel29195 , @v-xuxinyi-msft,

Thank you very much you guys save my day. working perfectly.

[Daniel29195]

@AllanBerces 

output : forecast_corrected is the column to achieve. 

steps : 

you need some column helpers ( although you can achieve it all in one column, but creating multiple columns for better undestanding ) . 

columns to calculate : 

planned -- given 

earned --  given 

forecasted --> 

forecasted = 
var planned = 'Table'[planned]
var earned = 25  --  dynamic base on your calculated measure i assume. 
var remaining = 
    SUMX(
        FILTER(
            'Table',
            'Table'[date]>= TODAY()
        ),
        'Table'[planned]
    )

var forecasted = 

        SWITCH(
            TRUE(),
            'Table'[date]< TODAY() , 0 , 
            'Table'[planned] >= earned ,  planned,
            'Table'[planned] < earned , earned 
        )


return forecasted

 remaining -->

remaining = 
    SUMX(
        FILTER(
            'Table',
            'Table'[date]>= TODAY()
        ),
        'Table'[planned]
    )

cumul_forecasted --> 

cumul forecasted = 
var cumul_foreacsted = 
SUMX(
    FILTER(
        'Table',
        'Table'[date] <=EARLIER('Table'[date])
    ),
    'Table'[forecasted]
)


return cumul_foreacsted

forecasted correct --> 

forecasted corrected = 
var next_nb = 
SELECTCOLUMNS(
        OFFSET(-1, 
            SUMMARIZE(
                'Table',
                'Table'[date],
                'Table'[remaining],
                'Table'[cumul forecasted]
            ),
            ORDERBY('Table'[date], asc )
        ),
        "next_period" , 'Table'[cumul forecasted]
)


var res = 
SWITCH(
    TRUE(),
    'Table'[cumul forecasted] <= 'Table'[remaining]  ,   'Table'[cumul forecasted] - next_nb,
    abs('Table'[remaining] - 'Table'[cumul forecasted]) < 'Table'[earned] , 'Table'[remaining] -  next_nb
    )
    return res

let me know if this helps. 

If this hepled you solving your problem, make sure to give it a thumbs up and mark it as a solution so others can find it quickly .

[AllanBerces]

Hi @Daniel29195 , @v-xuxinyi-msft 

if you dont mine just want to ask, base from my example above the average earned is greater than my plan thats why i can finish my scope of work earlier than my Daily plan. How about if my average earned is lessthan my daily plan my scope of work will finish beyond my daily plan date. How can i refelect that..

 Let say instead of March 31 become April 7 or 8.

Thank you

[Daniel29195]

@AllanBerces 

forecasted modified : 

forecasted = 
var planned = 'Table'[planned]
var earned = 25  --  dynamic base on your calculated measure i assume. 

var forecasted = 

        SWITCH(
            TRUE(),
            'Table'[date]< TODAY() , 0 , 
            'Table'[planned] >= earned ,  planned,
            'Table'[planned] < earned , earned 
        )


return forecasted

[AllanBerces]

Hi @Daniel29195 @v-xuxinyi-msft, just want to ask regarding the solution above, instead of one category let say 4 or 5 category on the same table which part on the above solution i will change.

Thank you