Helps to develop a calculation measure, available actual per month

[Shiroe]

Hi, partners

I am having a problem calculating a measurement following this formula and I request your kind assistance.

actual available per month = total budget year - cumulative execution divided by 12 - month (n)

I would appreciate if you can help me.

[TheoC · Accepted solution]

Hi @Shiroe 

You will need to have a Date's table established. Refer to https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/ for a quick and easy table.  Ensure you have linked the Date column from the Date table to your Fact table.

Date = 

VAR MinYear = YEAR ( MIN ( 'Table'[Month] ) )
VAR MaxYear = YEAR ( MAX ( 'Table'[Month] ) )
RETURN
ADDCOLUMNS (
 FILTER (
 CALENDARAUTO( ),
 AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
 ),
 "Calendar Year", "CY " & YEAR ( [Date] ),
 "Month Name", FORMAT ( [Date], "mmmm" ),
 "Month Number", MONTH ( [Date] )
)

If you already have a Date table, then ignore the above. Then you should be able just to use the following measure:

Actual Available Per Month = 

VAR _SumExecution = SUM ( 'Table'[Execution] )
VAR _SumBudget = SUM ( 'Table'[Budget] )
VAR _Cumulative = TOTALYTD ( _SumExecution , 'Date'[Date] )
VAR _TotalBudget = CALCULATE ( _SumBudget , ALL ( 'Table' ) , ALL ( 'Date' ) )
VAR _MonthNumb = MAX ( 'Date'[Month Number] )

RETURN 

DIVIDE ( _TotalBudget - _Cumulative , 12 - _MonthNumb )

Hope this is what you want?

Below is a screenshot of the outputs and each column:

[TheoC]

@Shiroe in many of the responses to a number of solutions provided by Community members you have mentioned that you are using different measures and are trying to adapt those rather than using the solutions that we have put forward.  Can you kindly please take on the advice from some of the Community's most respected members and Community Support personnel in @amitchandak and @v-yalanwu-msft. You may find that their advice, and that of mine, may support you in getting to an outcome to your problem.

All the best either way in your BI journey however please understand that the Community is here to support, educate and share its knowledge through a collaborative environment. It is very rare that these type of communities exist, let alone with one of the greatest tech company's to grace our presence in Microsoft.  Please consider taking on the advice and support provided.

All the best mate.

[v-yalanwu-msft]

Hi, @Shiroe ;

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft · Accepted solution]

Hi, @Shiroe ;

Please try it .

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] ),
                    CALCULATE ( MAX ( Calendar[Date] ), ALLSELECTED ( Calendar ) ),
                    MONTH
                )
            )
        )
    )

If not right ,can you share simple data and the output you want? It's better to have a scenario that makes it easier to understand the results you want.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TheoC · Accepted solution]

Hi @Shiroe 

You will need to have a Date's table established. Refer to https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/ for a quick and easy table.  Ensure you have linked the Date column from the Date table to your Fact table.

Date = 

VAR MinYear = YEAR ( MIN ( 'Table'[Month] ) )
VAR MaxYear = YEAR ( MAX ( 'Table'[Month] ) )
RETURN
ADDCOLUMNS (
 FILTER (
 CALENDARAUTO( ),
 AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
 ),
 "Calendar Year", "CY " & YEAR ( [Date] ),
 "Month Name", FORMAT ( [Date], "mmmm" ),
 "Month Number", MONTH ( [Date] )
)

If you already have a Date table, then ignore the above. Then you should be able just to use the following measure:

Actual Available Per Month = 

VAR _SumExecution = SUM ( 'Table'[Execution] )
VAR _SumBudget = SUM ( 'Table'[Budget] )
VAR _Cumulative = TOTALYTD ( _SumExecution , 'Date'[Date] )
VAR _TotalBudget = CALCULATE ( _SumBudget , ALL ( 'Table' ) , ALL ( 'Date' ) )
VAR _MonthNumb = MAX ( 'Date'[Month Number] )

RETURN 

DIVIDE ( _TotalBudget - _Cumulative , 12 - _MonthNumb )

Hope this is what you want?

Below is a screenshot of the outputs and each column:

[TheoC]

Hi @Shiroe 

Here is the PBIX - feel free to use as you wish 🙂

Hope this helps 🙂

[Shiroe]

Hi, TheoC

Sorry to answer so far.

Theo , Tried using your solution but it didn't work. This is the formula that I am using, it is not the same as the problem but it follows its logic and I require it for two other calculations. 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 division 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, so until December it is 0.

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

[TheoC]

Hi @Shiroe 

Here is the PBIX - feel free to use as you wish 

Hope this helps 🙂

[amitchandak]

@Shiroe , How is budget stored one way is , with help from date table

Diff  = sum(Table[Budget]) -  CALCULATE(SUM(Sales[exectiont]),DATESYTD('Date'[Date],"12/31"))

or use ytd for budget too

refer this can help

Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

[Shiroe]

Hi, Amit

Sorry to answer so far.

Amit , Tried using your solution but it didn't work. This is the formula that I am using, it is not the same as the problem but it follows its logic and I require it for two other calculations. 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 division 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, so until December it is 0.

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