Choose a date and return relevant values

[PhilJuniper]

I have a risks table, as below.

I have a powerbi report that has a drop down that selects year month, eg 2024-07. Currently this is a text field.

In the report is a table that returns the risk information.

I would like to return risks where the start date is equal to or before the end of the selected month, and the finish date is equal to or after the start of the selected month. 

Is this possible?

Risk #	Risk Description	Start Date	End date
1	David, watch your mouth. You come here and kiss your mother before you go, come here.	01/01/2024	31/12/2024
2	Doc, look, all we need is a little plutonium.	31/01/2024	30/01/2025
3	Good, I'll see you tonight. Don't forget, now, 1:15 a.m., Twin Pines Mall.	01/03/2024	01/03/2025
4	Good. Have a good trip Einstein, watch your head.	31/03/2024	31/03/2025
5	How could I have been so careless. One point twenty-one gigawatts. Tom, how am I gonna generate that kind of power, it can't be done, it can't.	30/04/2024	30/04/2025
6	I have to tell you about the future.	30/05/2024	30/05/2025
7	Let's get you into a radiation suit, we must prepare to reload.	29/06/2024	29/06/2025
8	Lorraine, are you up there?	29/07/2024	29/07/2025
9	Mom, is that you?	28/08/2024	28/08/2025

[mickey64 · Accepted solution]

Step 0: I use these data table below.

- 'Risk' Table -

- 'Date' Table -

Step 1: I make 4 measures below.

    M_Date_Sel = SELECTEDVALUE('Date'[yyyy-mm])

    M_Start Date_Sel = LOOKUPVALUE('Date'[Start of the month],'Date'[yyyy-mm],[M_Date_Sel])

    M_End Date_Sel = LOOKUPVALUE('Date'[End of the month],'Date'[yyyy-mm],[M_Date_Sel])

    M_Flag = IF(MAX('Risk'[Start Date])<=[M_End Date_Sel]

                  &&MAX('Risk'[End date])>=[M_Start Date_Sel],"Yes","No")

Step 2: I make a slicer and a matrix.

- Slicer -

- Matrix -

Step 3: I set up the filter.

[amitchandak]

@PhilJuniper , seem like very similar to HR Active employee problem, with Date table you should be able to do so

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU