Forum Discussion
Time intelligence help!
Hi all -- i am trying to create a report with measures that utilize a date table but indirectly and having a heck of a time doing it. So I have a date table with a few sliced up versions of my date column, including year/month.
I have a second table with episodes of care (healthcare provider) with admission date and discharge date. My users want to be able to select a year/month and show the dates within that episode of care that are in the year/month. The episodes of care can extend over several months, but they want the ability to specifically see utilzation by month. I didn't create an active relationship between my episode table and date table because I didn't want any of the visuals to filter based on admission or discharge being in the selected month.
Few weird things I can't figure out:
-even with no relationship -- once i select a month, it filters the table and it looks pretty random to me. for example, i can select May, and it sporatically filters out records, not just may admits or may discharges
-I can't figure out how to create a dynamic current bed days for the month that is selected. for example, if the "episode" is from 4/27 - 5/13, I want to be able to select 2023/05 and see 13, not 16. Its not working at all, heres what i did (index is a unique identifer for each episode):
2 Replies
IC_SLFLORES Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hmm let me see if i can do a better job of explaining this..
so I have a date table wih the following:
Date Month Fiscal Year Year/Month etc.... I have a episode table with the following examples:
Unique Encounter ID Customer ID Admission Date Discharge Date 1 400 4/28/2023 5/14/2023 2 401 5/2/2023 5/14/2023 In my model schema i do not have a active/inactive relationship between the two tables. What my users want the ability to do is select 2023/05 and show the following:
unique encounter 1 - 14 days
unique encounter 2 - 12 days
or select 2023/04 and show the following:
unique encounter 1 - 2 days
unique encounter 2 - 0 days
They want to show the number of days between admission and discharge that are only in the selected month. I originally had a relationship between admission & date, but if the month is selected, it only shows episodes of care where the admission date is within the selected month. Thats not what they need - they need all episodes, and associated days for that episode within the selected month.
