Forum Discussion
Choose a date and return relevant values
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 |
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.
2 Replies
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-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZUStep 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.
