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

6 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    4 years ago

    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's avatar
      Shiroe
      Helper I
      4 years ago

      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's avatar
        AllisonKennedy
        Community Champion
        4 years ago

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