Help with a measure on a simple model

[wrv505]

I have a simple data model of a "tblCases" table and an accompanying "tblCalendar" table with the following relationships:

Active Relationship: tblCalendar[Date] ---> tblCases[CreatedOn]

Inactive Relationship: tblCalendar[Date] ---> tblCases[Resolution Date]

tblCalendar has a column that returns the Monday date of any given date called [Week Starting Date (Monday)] and is what the data is to be grouped by (so we can see results for each week).

I have created a matrix visual grouped by [Week Starting Date (Monday)] as the rows and have added the following measures as values:

Total Created = CALCULATE(COUNTA(tblCases[CaseId]))

Total Resolved = CALCULATE(COUNTA(tblCases[CaseId]),USERELATIONSHIP(tblCalendar[Date],tblCases[Resolution Date]))

These work fine. However, I need another measure that shows me how many cases are still open at the end of the week. This isn’t as simple as just doing a calculation on the above 2 measures because there will be cases already open going into any given week that need to be taken into consideration too. The measure therefore instead needs to be a COUNTA of the [CaseId] field but to also include the following logic / filters:

tblCases[CreatedOn] < tblCalendar[Week Starting Date (Monday)] +7 (on the relevant week in matrix visual)

AND

(tblCases[Resolution Date] >= tblCalendar[Week Starting Date (Monday)] +7 (on the relevant week in matrix visual) OR

tblCases[Resolution Date] = BLANK)

I’ve tried many variations of formulas but can’t seem to get it right. I also tried creating a separate Date table with columns replicating the totals from the measures, but I need the ability to slice the data by other fields and also see the underlying records in tblCases so this didn’t work.

Any ideas are welcome.

[amitchandak · Accepted solution]

@wrv505 , refer if the blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[wrv505]

Perfect, thank you.

[Greg_Deckler]

@wrv505 - 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.