Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
The Data Model
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.
Solved! Go to 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 )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
User | Count |
---|---|
57 | |
22 | |
21 | |
19 | |
16 |
User | Count |
---|---|
87 | |
87 | |
52 | |
37 | |
23 |