How to Filter a Power BI Measure by Selected Dates

Learn how to write a measure that will do a calculation as of a certain date forward.

Topic: Power BI, measures, DAX language
Functions: AVERAGE, CALCULATE, SELECTEDVALUE, FILTER, ALL
To jump to the final measure, go to this point in the video: 10:09

This was not mentioned in the video, but I would recommend making your slicer "single select" so that the "Average_Life_Expectancy_As_Of_Date_Selected" measure works as expected. To do this, click the slicer visual, go to the format visual pane, click slicer settings, and toggle single select to on.

If you would like to keep multi-select, I would recommend amending your code to the function below. You will see that the COALESCE and MIN lines are new (third to last line and last line). You may want to use MAX instead of MIN if you'd prefer to default to the latest selected date and forward. Keep it MIN if you'd prefer to see the earliest selected date and forward.

Average_Life_Expectancy_As_Of_Date_Selected =
CALCULATE(
'Measure'[Average_Life_Expectancy],
FILTER(
ALL(dsbb1_lifeexpectancy[Year]),
dsbb1_lifeexpectancy[Year] *INSERT GREATER THAN OR EQUAL TO SIGN HERE*
COALESCE(
SELECTEDVALUE(dsbb1_lifeexpectancy[Year]),
MIN(dsbb1_lifeexpectancy[Year])
)
)
)

*Note: YouTube does not support angled brackets in video details so you will have to update this inside of Power BI Desktop (SEE ALL CAPS ABOVE)

#PowerBI #measures #DAX #dates #DSBB

[joelapsansky]

Welcome to all the new subscribers! New video on anomaly detection and forecasting (Power BI built-in Machine Learning) coming later today. Early next week, I'll release another video on using relative dates, which is a dynamic way to filter in Power BI. Have a great weekend.

[andredeoliveira]

I was looking for this explanation, thanks

[claudialuciawerneck]

Thanks for your help! Wish you success🍀!

[abirhossain]

Hi @joelapsansky. Got a different question. Would be kind if you can help me again. I need to find Utilization/ Occupancy (percentage over time) for different charging stations. Lets say, I have data with different charge stations used for last 60 days with start time and end time. I also created two calculated tables "DimDate" with dates, week, day and another table "time" which has two columns "time start" and "time end" to make 1 hour time interval each row. How to proceed with these to find utilization of different stations (percentage over time). Could you please guide through the steps or suggest some resources. Thanks for your time!

[smb_insights]

Thank you very much. You saved me bro.

[aniciao]

Hi @joelapsansky. Your solution shows how to filter a single selected date. How do I filter for a selected date range in a slicer for eg. from 1 Nov 2023 till 30 Nov 2023.

[santiago]

This is exactly what I need. But it doesn't work in my report! :'C

[abirhossain]

Hi. Lets say I wanna find average transactions per day. I have slicer where I choose last 30 days. Bt when I choose a customer who has 10 transactions in just 5 days among our chosen 30 days. From my formula, I get 10/5 =2 per day for that customer instead of 10/30. Kindly let me know how I can fix it. Thanks