If actuals, display actuals, else display budget

DAX DAX Calculations
1

We want to create a matrix where in the columns, we will have the fiscal year by month. In the rows we have departments, where each department has a monthly budget for the year. Can I display actuals in the months that are posted, and budgets for the months that are not posted? Like this image. Just looking for discussion on if it’s possible and top level, how you go about it.
Capture|690x177

2

@Usates,

Using the Practice Dataset tool, I put together an example showing how this could be done:

Created a basic disconnected monthly budget table based on a 5% increase over 2020 monthly sales:

Budgets =

CALCULATETABLE (
    ADDCOLUMNS (
        SUMMARIZE ( Dates, Dates[Year], Dates[Month & Year] ),
        "Monthly Budget", ( CALCULATE ( [Sales], DATEADD ( Dates[Date], -1, YEAR ) ) * 1.05 )
    ),
    Dates[Year] = 2021
)

Then created a measure to test whether actual sales existed for the Channel and Month/Yr combination in the matrix. If Sales were not blank, returned actual sales, if they were, returned lookup values from the budget table:

Actual and Budget Values =
VAR SelChannel = SELECTEDVALUE( Channels[Channel Name] )
VAR SelMonthYr = SELECTEDVALUE( Dates[Month & Year] )

VAR Test =
IF( [Sales] <> BLANK(),
    [Sales],
    LOOKUPVALUE( Budgets[Monthly Budget], Budgets[Month & Year], SelMonthYr )
)

RETURN
Test

image
image1011×128 20.1 KB

In actuality, your budget table likely would be more granular, requiring the lookup to be done via multiple filter conditions and TREATAS, but this hopefully conveys the general approach that could be used.

I hope this is helpful. Full solution file attached.

3 Likes
3

Thank you