topic Dax measure for if date is smaller than maximum selected date from slicer then sumA else SumB in DAX Commands and Tips

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

ILLANA_N — Mon, 16 Jun 2025 11:57:11 GMT

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.

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

bhanu_gautam — Tue, 17 Jun 2025 15:12:01 GMT

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])
)

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

Jihwan_Kim — Wed, 18 Jun 2025 03:03:36 GMT

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] )

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

Anonymous — Thu, 19 Jun 2025 04:37:50 GMT

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.

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

Anonymous — Mon, 23 Jun 2025 04:18:01 GMT

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.

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

Anonymous — Thu, 26 Jun 2025 07:06:17 GMT

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.