Forum Discussion
Need help coonecting caselaod status spanning date range
I have caseloads with differetn statuses ie compelted active waitlist
i have the data connected to custom date table with quarters
when i look into a matrix visualization i want to see if someone is active in the caselaod they should count for each quarter they were active till rpesent..if compelted to count in the quarter they were completed..ie
i have two relationship two the date table one active and one inactive start and end
how can i should the progress of a client throguhout different quarters
4 Replies
how can i connect date field to a range of dates....to capture all the quarters of a caseload...if osmeone start ins january and finishing in may... I want them to ocunt in Q1, and Q2...how do i set this up
Hi arjans14 ,
Given your setup with two relationships to the date table (one active and one inactive for start and end dates), here's a concise action plan to help you visualize client progress through different quarters:
1. Use a Matrix Visualization: Since you're already looking into a matrix visualization, ensure it's configured to display your caseload statuses across the quarters. The matrix visual is ideal for this purpose as it supports a stepped layout, which can effectively display data across multiple dimensions (statuses and time).
Visualization types in Power BI - Power BI | Microsoft Learn
2. Manage Relationships: Ensure that the relationships between your caseload data and the date table are correctly set up to reflect the active and inactive statuses with the appropriate start and end dates.
Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn
3. DAX Measures for Status Count: Create DAX measures to count the number of caseloads in each status for every quarter. You'll need measures that can calculate the count of active, completed, and waitlisted statuses based on the quarter.
4. Visualize and Analyze: Once you have your matrix set up with the appropriate relationships and DAX measures, you should be able to visualize the progress of clients through different quarters.
The exact program will have to be tailored to your situation, and without sample data I can only offer these suggestions. If you can, please provide us with sample data with the privacy content removed!
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.okay this is what i went with i created a measuer
Caseload Progress =VAR MaxDate = MAX('Date'[Date])VAR MinDate = MIN('Date'[Date])RETURNCALCULATE(COUNTROWS(FILTER('Individual Plans','Individual Plans'[startdateserviceplan]<=MaxDate&&'Individual Plans'[status dates]>=MinDate)),CROSSFILTER('Date'[Date],'Individual Plans'[status dates],None))
this measure works great...only problem is countrows is showing all rows...there is a few cases with the same id...if the id is the same i woudl want the earliest startdate and maxstatus date..how would i go about that
also the date table is connecteed to status dates which has a formula as wellstatus dates =IF('Individual Plans'[status] = "Active",TODAY(),IF('Individual Plans'[status] = "Waitlist",'Individual Plans'[dateentered],IF('Individual Plans'[status] = "Completed",'Individual Plans'[enddateserviceplan],IF('Individual Plans'[status] = "Closed",'Individual Plans'[senddateserviceplan],'Individual Plans'[dateentered] // For any other status, use 'dateentered'))))
so it basically is showing from start to the status date depednign ont he status...let me know what you thinkthis measure works exactly the way i want for time intellgience event progress
Caseload Progress =
VAR MaxDate = MAX('Date'[Date])
VAR MinDate = MIN('Date'[Date])
RETURN
COUNTROWS(
SUMMARIZE(
FILTER(
'Individual Plans',
'Individual Plans'[startdateserviceplan] <= MaxDate &&
'Individual Plans'[status dates] >= MinDate
),
'Individual Plans'[id],
"MinStartDate", MIN('Individual Plans'[startdateserviceplan]),
"MaxStatusDate", MAX('Individual Plans'[status dates])
)
)
