Dax measure for if date is smaller than maximum selected date from slicer then sumA else SumB

[ILLANA_N]

I want to show a matrix table showing actuals if dates is smaller than e.g. 31 May 2025 then budget values if after this date

I was using this formula

FORECAST = IF(FIRSTDATE(Budget_data[Date]) <= DATE(2025,05,31),sum('PnL Data_2025'[Amount]),sum(Budget_data[Amount]))

but I now want the dashboard user to select a dates on the slicer instead of me hard coding the cut off date.

[v-hjannapu]

Hi @ILLANA_N,

Thank you  for reaching out to the Microsoft fabric community forum.

Thank you so much @Jihwan_Kim, for sharing such a clear and practical solution really appreciate the effort you put into recreating the sample model and measure logic

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please Accept it as a solution so that other community members can find it easily.
Regards,
Harshitha.

[v-hjannapu]

Hi @ILLANA_N,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Thank you.

[v-hjannapu]

Hi @ILLANA_N,

May I ask if you have resolved this issue? If so, please mark the helpful reply and Accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[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.

Expected result measure: = 
VAR _selecteddate =
    SELECTEDVALUE ( 'Calendar slicer'[date], DATE ( 2025, 5, 31 ) )
VAR _pnldata =
    SUM ( 'PnL Data_2025'[amount] )
VAR _budget =
    SUM ( Budget_data[amount] )
VAR _t =
    ADDCOLUMNS (
        VALUES ( 'Calendar'[date] ),
        "@result",
            IF (
                'Calendar'[date] <= _selecteddate,
                CALCULATE ( SUM ( 'PnL Data_2025'[amount] ) ),
                CALCULATE ( SUM ( Budget_data[amount] ) )
            )
    )
RETURN
    SUMX ( _t, [@result] )

[bhanu_gautam]

@ILLANA_N Create a date slicer in your Power BI report.
Use the SELECTEDVALUE function to get the date selected by the user.

DAX
FORECAST =
VAR SelectedDate = SELECTEDVALUE(DateTable[Date])
RETURN
IF(
SelectedDate <= DATE(2025, 05, 31),
SUM('PnL Data_2025'[Amount]),
SUM(Budget_data[Amount])
)