How do I show monthly compliance?

[CarloA93]

I have a dataset of patient weights, and they want me to be able to show whether or not a weight for a patient was taken at least once a month. They want it to be expressed as a percentage for each Quarter, both individually and for a group home. (example: for Q1, patient A had a weight recording for 2 of the 3 months, or 66% compliance.)

The problems I am running into are:

1) How do I show whether or not a recording was taken for each month, and then going along with this:

2) If a patient is new, then not having any recordings wouldn’t be differentiated from not having a recording, for example, if they moved into the group home in the middle of the quarter.

3) Some patients have multiple recording in a single month, but I really only need to show whether or not a recording was taken.

I can show how many for a month in a different visual, and I can approximate a percentage based on counts per expected value, but it doesn’t take into account multiple recordings.

Patient Name	Date	Weight	Group Home	Case Manager
DOE, JOHN	15-Sep	195	Wheatland	Arnold Jones
DOE, JOHN	15-Aug	195.8	Wheatland	Arnold Jones
CHAN, LESLIE	15-Sep	180.6	Wheatland	Arnold Jones
CHAN, LESLIE	15-Aug	181	Wheatland	Arnold Jones
ROCKS, CALLUM	15-Sep	160.8	Wheatland	Arnold Jones
ROCKS, CALLUM	15-Aug	161	Wheatland	Arnold Jones
ROCKS, CALLUM	17-Jul	163	Wheatland	Arnold Jones

[lbendlin · Accepted solution]

Here is one possible implementation.  

[v-yadongf-msft · Accepted solution]

Hi @CarloA93 ,

This is my test table:

Please try following DAX to create new columns:

MonthNumber = MONTH('Table'[Date])

Jul 22 = 
var CountJuly = COUNTX(FILTER('Table','Table'[Patient Name] = EARLIER('Table'[Patient Name]) && 'Table'[MonthNumber] = 7), 'Table'[MonthNumber])
return
IF( CountJuly >=1,"Yes","No")

Aug 22 = 
var CountAug = COUNTX(FILTER('Table','Table'[Patient Name] = EARLIER('Table'[Patient Name]) && 'Table'[MonthNumber] = 8), 'Table'[MonthNumber])
return
IF( CountAug >=1,"Yes","No")

Sep 22 = 
var CountSep = COUNTX(FILTER('Table','Table'[Patient Name] = EARLIER('Table'[Patient Name]) && 'Table'[MonthNumber] = 7), 'Table'[MonthNumber])
return
IF( CountSep >=1,"Yes","No")

Percent Compliance (Quarter) = 
var Count_Jul22 = COUNTX(FILTER('Table','Table'[Patient Name] = EARLIER('Table'[Patient Name]) ), 'Table'[Jul 22])
var Count_Aug22 = COUNTX(FILTER('Table','Table'[Patient Name] = EARLIER('Table'[Patient Name]) ), 'Table'[Aug 22])
var Count_Sep22 = COUNTX(FILTER('Table','Table'[Patient Name] = EARLIER('Table'[Patient Name]) ), 'Table'[Sep 22])
var CountYes_Jul22 = COUNTX(FILTER('Table','Table'[Patient Name] = EARLIER('Table'[Patient Name]) && 'Table'[Jul 22] = "Yes"), 'Table'[Jul 22])
var CountYes_Aug22 = COUNTX(FILTER('Table','Table'[Patient Name] = EARLIER('Table'[Patient Name]) && 'Table'[Aug 22] = "Yes"), 'Table'[Aug 22])
var CountYes_Sep22 = COUNTX(FILTER('Table','Table'[Patient Name] = EARLIER('Table'[Patient Name]) && 'Table'[Sep 22] = "Yes"), 'Table'[Sep 22])
return
DIVIDE(CountYes_Jul22 + CountYes_Aug22 + CountYes_Sep22, Count_Jul22 + Count_Aug22 + Count_Sep22)

Percent Compliance (Quarter)/House = IF('Table'[Date] = MAXX(FILTER('Table','Table'[Patient Name] = EARLIER('Table'[Patient Name])),'Table'[Date]),'Table'[Percent Compliance (Quarter)])

The result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yadongf-msft]

Hi @CarloA93 ,

Has your problem been solved? If solved, please consider Accept it as the solution to help the other members find it more quickly.

Best regards,

Yadong Fang

[Ashish_Mathur]

Hi,

Share a larger dataset and show the expected result.

[CarloA93]

Here's a slightly larger dataset:

Patient Name	Date	Weight	House	Manager
LARSON, STEVEN	9/15/2022	195	Wheatland	Emily Troxell
LARSON, STEVEN	8/15/2022	195.8	Wheatland	Emily Troxell
KEITH, KEVIN	9/15/2022	180.6	Wheatland	Emily Troxell
KEITH, KEVIN	8/15/2022	181	Wheatland	Emily Troxell
TRUMAN, FAITH	9/15/2022	160.8	Wheatland	Emily Troxell
TRUMAN, FAITH	8/15/2022	161	Wheatland	Emily Troxell
TRUMAN, FAITH	7/17/2022	163	Wheatland	Emily Troxell
CHOW, MARIE	4/15/2022	227	Wheatland	Emily Troxell
CHOW, MARIE	9/15/2022	230.4	Wheatland	Emily Troxell
CHOW, MARIE	8/15/2022	231.8	Wheatland	Emily Troxell
CHOW, MARIE	7/15/2022	234.2	Wheatland	Emily Troxell
CHOW, MARIE	5/15/2022	236.2	Wheatland	Emily Troxell
CHOW, MARIE	6/15/2022	236.4	Wheatland	Emily Troxell
MCMURRAY, JANE	8/15/2022	161.6	Wheatland	Emily Troxell
MCMURRAY, JANE	7/15/2022	163	Wheatland	Emily Troxell
MCMURRAY, JANE	5/15/2022	164.8	Wheatland	Emily Troxell
MCMURRAY, JANE	6/15/2022	165.2	Wheatland	Emily Troxell
MCMURRAY, JANE	9/15/2022	166.4	Wheatland	Emily Troxell
DOE, JOHN	7/12/2022	213.4	Wheatland	Emily Troxell
DOE, JOHN	7/19/2022	214	Wheatland	Emily Troxell
DOE, JOHN	8/2/2022	214.6	Wheatland	Emily Troxell
DOE, JOHN	7/26/2022	215.8	Wheatland	Emily Troxell
DOE, JOHN	8/16/2022	216.4	Wheatland	Emily Troxell
DOE, JOHN	9/6/2022	216.4	Wheatland	Emily Troxell
DOE, JOHN	8/30/2022	217	Wheatland	Emily Troxell
DOE, JOHN	8/9/2022	217.2	Wheatland	Emily Troxell
DOE, JOHN	8/23/2022	217.8	Wheatland	Emily Troxell
DOE, JOHN	9/13/2022	220.2	Wheatland	Emily Troxell

I would essentially want to show whether or not there was a recording each month, and then be able to calculate the percentage of the time a recording was taken to meet the minimum requirement that each individual as well as each household.

For example:

	Jan 22	Feb 22	Mar 22	Percent Compliance (Quarter)
PersonA (House A)	Yes (if there is at least 1 recording for the month)	Yes	No	66%
PersonB (House A)	No	No	Yes	33%
PersonC (House B)	No	No	No	0%
PersonD (House B)	Yes	No	No	33%

	Percent Compliance
House A	50% (i.e. 3 out of 6 expected recordings)
House B	16.7%

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[lbendlin · Accepted solution]

Here is one possible implementation.  

[CarloA93]

This looks like a step in the right direction. I'll play around with the pbix you attached and see what I can do with my dataset.