How do I show monthly compliance?

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 Q...

Need Help

lbendlin
4 years ago
Here is one possible implementation.

measurements taken.pbix24 KB

v-yadongf-msft

4 years ago

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)])