Calculate/Visualize Time Duration Dynamically Based on Selected Values in Date Slicer / Row Context

[Anonymous]

Dear PBI Community Members,

I have a data file about children in child care service: the table has service begin date ('entry_date') and service end date ('exit_date') for each child, or censoring date (the last date of study period) for those still in service at end of fiscal year. I plan to visualize the length of service days for each child. I can caluclate the length of stay ('Los') by using the difference between (exit_date-entry_date). I have categorized the Los into a categorical field with several categories (a one week, b 9 days to 1 mont, c 1 to 3 months, etc.). I have generated the following visuals, showing the 'Los' categories for children who entered care during each month of FY2023 or in care service at end of each month. The month year period is from a calendar table, which is connected with 'entry_date' in the service table. The number of children entering care and in care are the two measures that I created from the date and care tables.    IN the 1st matrix table visual (children entries), the Los categorical field is used as the column field. In the 2nd visual (children in care), the Los field is used as the legend. 

As you can see, the Los field is calculated as the length until the exit date. I would like to calculate a new Los field 'los_new', which is is the length of stay until the (begining or end of) current date period selected. That is Los_new=current date selected-entry_date. 

I would also like to create a categorical field based on 'Los_new' and used it as a table column or legend in the bar chart.  

Is there any way I cab achieve this using Power BI? Any suggestions/advice are appriciated.     

Thanks.    

[amitchandak · Accepted solution]

@Anonymous , for that you need a measure like

Sumx(Table,datediff( Max(selectedvalue(Date[Date]) , Table[Start Date]), Min(selectedvalue(Date[Date]) , Table[Exit Date]), Day))

As this a measure, you need segmentation for frequency

example

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

Also joins with the date table can be active or inactive based on the need, refer files if needed