AVG Forward Badge help!!!

[Shiroe]

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 infinitely grateful. I have tried everything. I don't know what else to do to make the measurement work correctly respecting the filters. I need to solve the denominator of the divide since always both in the calculation that I propose in this post and in the one that I show you, I need to dynamically subtract the month in which it is located at twelve months, that is, if it is January (12 - 1 = 11), if it is February (12 - 2 = 10),etc.. so until December it is 0.

Original formula

total budget for the year - accumulated budget/12-Month(n)

DAX

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

RETURN

IF(SUM('Consolidated budget'[Budget Value])=0,BLANK(),CALCULATE (DIVIDE (TotalBadge-AcumBadge,DATEDIFF(MAX(Calendar[Date]),Here should go the maximum date of the year that is filtered but I can not obtain it without leaving it as a constant,MONTH))))

[AllisonKennedy]

@Shiroe  - what visual context are you using this within? How does Power BI know which month it is located in? 

When you say 'maximum date of the year this is filtered' do you mean maximum date in the Fact table? 

Have you tried: 

DATEDIFF(MAX(Calendar[Date]),MAX(Fact[Date],MONTH)

[Shiroe]

Hi , @AllisonKennedy 

The real problem is that I need to keep the budget total for a specific year that can change with a filter on one variable. Could you help me ? Please 😞

[AllisonKennedy]

@Shiroe  Can you provide a sample file please via OneDrive link? I'm struggling to see your big picture from what you've described so far (sorry, trying to catch up but haven't invested all the hours into it I'm sure you have! 😄)

[Shiroe]

Hi, @AllisonKennedy 

I am attaching the test file that I am using. If you see I have mess in _totalbudget and in the return Actual Available Per Month result. I can only pray if you can help me

by the way the formulas in this file are different. The entire calculation process is separated and the Actual Available Per Month measure contains everything within itself to facilitate the review

[AllisonKennedy]

@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 · Accepted solution]

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