Calculate measure showing coming month based on Actual & Budget

[HLVW]

Hi.

I am trying to calculate the column "Coming months", pls. see below table. I have a measure for Actual and Budget figures and I would like a measure (based on the 2 existing measures) to calculate the expected turnover for the rest of the year. Current month should be Budget minus Actual - unless Actual is higher than Budget for the month. Earlier months should be 0 and future months should be Budget.

	Actual	Budget	Coming months	Forecast
Jan	10.500	12.500	-	10.500
Feb	12.300	12.500	-	12.300
Mar	9.530	12.500	-	9.530
Apr	5.420	12.500	-	5.420
May	12.035	12.500	-	12.035
Jun	2.500	12.500	10.000	12.500
Jul		12.500	12.500	12.500
Aug		12.500	12.500	12.500
Sep		12.500	12.500	12.500
Oct		12.500	12.500	12.500
Nov		12.500	12.500	12.500
Dec		12.500	12.500	12.500

Thanks a lot in advance.

[HLVW]

Below measure solved my problem:

Forecast (budget less Actual):= VAR CurrentMonthEnd = EOMONTH(TODAY(), 0)
VAR CurrentMonthBegin = EOMONTH(TODAY(),-1)
VAR Actual = [Invoiced sales]
VAR Budget = [Budget Sales]
VAR Result =
SWITCH (
TRUE(),
MIN('Date'[Date]) < CurrentMonthBegin,
0,
MIN('Date'[Date]) > CurrentMonthEnd,
Budget,
MIN('Date'[Date]) > CurrentMonthBegin && Actual > Budget, 0,
Budget - Actual)

RETURN
Result

Thanks a lot for your suggestions.

[v-xinruzhu-msft]

Hi, 

Thanks for the solution @AnalyticsWizard  provided, and i want to offer some more information for user to refer to.

hello @HLVW , based on your description, you can refer to the following solution.

Sample data is the same as yours.

First I create a mon_no measure.

Month_No = MONTH(CONVERT(MAX('Table'[Month])&" "&1,DATETIME))

2.Create a coming month measure.

Coming Month =
VAR _maxmonth =
    MAXX ( FILTER ( ALL ( 'Table' ), [Actual] <> BLANK () ), [Month_No] )
RETURN
    SWITCH (
        TRUE (),
        [Month_No] < _maxmonth, 0,
        [Month_No] = _maxmonth, [Budget] - [Actual],
        [Budget]
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AnalyticsWizard]

@HLVW 

You can create a measure for "Coming months" that calculates based on the criteria you provided by comparing "Actual" and "Budget" for the current month and then providing the Budget for future months.

First, you'll need to create a measure that identifies the current month. Let's assume you have a 'Date' column in your table and you are considering the latest date in your data as the current month for this calculation.

Here's a DAX measure that you can use for "Coming months":

Coming Months =
VAR CurrentMonth = EOMONTH(TODAY(), 0)
VAR Actual = [Actual Measure] // Replace with your actual measure
VAR Budget = [Budget Measure] // Replace with your actual measure
VAR Result =
IF (
MIN('DateTable'[Date]) > CurrentMonth,
Budget,
IF (
MIN('DateTable'[Date]) = CurrentMonth,
IF (Actual > Budget, 0, Budget - Actual),
0
)
)
RETURN
Result

Here’s a breakdown of the measure:

1. `CurrentMonth` gets the end of the current month based on today's date.

2. `Actual` and `Budget` are placeholders for your existing measures.

3. In `Result`, it uses a conditional `IF` statement to determine what to return:
- If the month is greater than the current month, it returns the Budget.
- If the month is the current month, it compares Actual to Budget and returns the appropriate value (0 if Actual is greater than Budget, otherwise the difference).
- If the month has passed (less than the current month), it returns 0.

Make sure your date table (`DateTable`) is correctly set up with a continuous date range and that it is linked to your data. You may need to adjust the `CurrentMonth` calculation if you consider a different logic for determining the current month, like the maximum date in your dataset instead of today's date.

Apply this measure to your visual, and it should display the values for "Coming months" as you described.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[HLVW]

Hi @AnalyticsWizard 

Thanks a lot for your reply.

It is almost there 🙂

I just have a problem with the current month as it shows zero - and the actual figure is less than budget.

Any idea what I might be missing?