Forum Discussion

Shiroe's avatar
Shiroe
Helper I
4 years ago
Solved

AVG Forward Badge help!!!

Hi partners,   I am desperate trying to solve this formula, I kindly request your help.   This is the formula that I am using. If you can help me find the error and give a solution, I will be inf...
Need Help
Tutorial Requests
  • Shiroe's avatar
    Shiroe
    4 years ago

    Hello my friend

    After much suffering, this was the formula that worked. sorry to reply late. I leave the formula in case someone can serve you

     

    AVG Forward Badge =
    VAR TotalBadge = CALCULATE(SUM('Consolidated budget'[Budget Value]),FILTER(ALL(Calendar),Calendar[Year]))
    VAR AcumBadge = CALCULATE([SumBudget],FILTER(ALLSELECTED(Calendar), Calendar[Month] <= MAX(Calendar[Month])))
    VAR Month = CALCULATE(MAX(Calendar[Month]),FILTER(ALLSELECTED(Calendar), Calendar[Month] <= MAX(Calendar[Month])))
    RETURN
    IF(SUM('Consolidated budget'[Budget Value])=0,BLANK(),CALCULATE (DIVIDE (TotalBadge-AcumBadge,12 - Month)))
AllisonKennedy's avatar
AllisonKennedy
Community Champion
4 years ago

Shiroe  Does this get close to what you want? 

 

AK_AvgForward Badge =
DIVIDE([_TotalBudget]-[_Cumulative], 12 - MAX('Calendar'[Month])) 
 

 

Not sure if you want to use _Cumulative or if that should be using budget instead of Execution values???

Shiroe's avatar
Shiroe
Helper I
4 years ago

Hello my friend

After much suffering, this was the formula that worked. sorry to reply late. I leave the formula in case someone can serve you

 

AVG Forward Badge =
VAR TotalBadge = CALCULATE(SUM('Consolidated budget'[Budget Value]),FILTER(ALL(Calendar),Calendar[Year]))
VAR AcumBadge = CALCULATE([SumBudget],FILTER(ALLSELECTED(Calendar), Calendar[Month] <= MAX(Calendar[Month])))
VAR Month = CALCULATE(MAX(Calendar[Month]),FILTER(ALLSELECTED(Calendar), Calendar[Month] <= MAX(Calendar[Month])))
RETURN
IF(SUM('Consolidated budget'[Budget Value])=0,BLANK(),CALCULATE (DIVIDE (TotalBadge-AcumBadge,12 - Month)))