Forum Discussion

Post Prodigy

2 years ago

Solved

Forecast Distribution

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

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]
)

Anonymous

2 years ago

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.

Forecast Distribution.pbix43 KB

8 Replies

Daniel29195

Community Champion

2 years ago

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
Post Prodigy
2 years ago
Hi Daniel29195 , Anonymous,
Thank you very much you guys save my day. working perfectly.

Anonymous

Not applicable

2 years ago

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.

Forecast Distribution.pbix43 KB

Daniel29195

Community Champion

2 years ago

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 .

Daniel29195
Community Champion
2 years ago
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
  Post Prodigy
  2 years ago
  Hi Daniel29195 Anonymous, 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
AllanBerces
Post Prodigy
2 years ago
Hi Daniel29195 , Anonymous
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

AllanBerces
Post Prodigy
2 years ago
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 , Anonymous for providing the solutions below

Thank you

Forum Discussion

Forecast Distribution

8 Replies

Recent Discussions

Container size fitting

Expand Hierarchy bottom up

Multiple entities now returning blank/0 across several dashboards

How to divide a grouped actual Income/pay as a %

Power BI – Best approach for a 300-field Field Picker with RLS and performance concerns