MTD Actual Compared to Monthly Budget

[reportwriter001]

The Data Model

1. DimDate Table
   1. Every day of the year
   2. Columns include:
      1. dimDateID
      2. DateName
2. Actual YTD Units Sold
   1. We take snap shots on a weekly basis of our YTD sold units
   2. Includes a dimDateID
3. Budgeted YTD Units by Month
   1. We budget what we expect our YTD sold units will be at the end of each month
   2. Includes dimDateID

The Problem Statement

Below is a screenshot of the current state of the data as of Dec 8, 2023.  I have budgeted and actual YTD unit numbers for Oct 31, 2023 and Nov 30, 2023.  No issues there.  I recently took a snapshot of our YTD unit numbers on Dec 7, 2023.  I need to compare the Dec 7, 2023 actual units with the Dec 31, 2023 budget.  I can't figure out how to write the DAX measure that says, "sum the Budget Units if the End of Month value for DateName in the Actuals table is equal to a DateName in the Budget table.

Thank you in advance for any assistance.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your semantic model.

Actual units measure: = 
VAR _monthcumulate =
    CALCULATE ( SUM ( Actual[actual_units] ), DATESMTD ( Dim_Date[Date] ) )
VAR _currentrowdate =
    MAX ( Dim_Date[Date] )
VAR _endofmonthdate =
    EOMONTH ( _currentrowdate, 0 )
VAR _conditiontable =
    FILTER (
        ADDCOLUMNS (
            FILTER ( ALL ( Dim_Date ), EOMONTH ( Dim_Date[Date], 0 ) = _endofmonthdate ),
            "@actualunits", CALCULATE ( SUM ( Actual[actual_units] ) )
        ),
        [@actualunits] <> BLANK ()
    )
VAR _conditionmaxdate =
    MAXX ( _conditiontable, Dim_Date[Date] )
RETURN
    SWITCH ( TRUE (), _conditionmaxdate = _currentrowdate, _monthcumulate )

Budget units measure: =
VAR _currentrowdate =
    MAX ( Dim_Date[Date] )
VAR _endofmonthdate =
    EOMONTH ( _currentrowdate, 0 )
VAR _budget =
    CALCULATE (
        SUM ( Budget[budgeted_units] ),
        TOPN (
            1,
            FILTER ( ALL ( Dim_Date ), EOMONTH ( Dim_Date[Date], 0 ) = _endofmonthdate ),
            CALCULATE ( SUM ( Budget[budgeted_units] ) )
        )
    )
RETURN
    IF ( NOT ISBLANK ( [Actual units measure:] ), _budget )

[reportwriter001]

Thanks Jihwan.  This was spot on and extremely helpful.  Although my actuals are YTD and so I didn't need your actual measure, I was able to copy the budget measure and simply replace with my table/column names.  Thank you so much for your time and assistance on this.