different filter criteria applied to the same measures to yield different outputs. how to do that?

[Anonymous]

Hi wonderful folks at POWERBI community,

I developed certain measures based on 11 months of data and there is one measure needed to be calculated based on 12 months of data. I need to multiple these 2 types of measures. The pag-level filters don't work well with built-in filters. Is there an easy way to do so? I have found it easy to do it in Excel but harder to replicate in PowerBi. Any suggestions? 

 Below is the sample data: 

Table1

visit_id	fiscal_month	bill_status	clinic	total_payment
1	July	closed	clinicA	100
2	Aug	billed	clinicB	10
3	Sept	closed	clinicC	12
4	June	closed	clinicD	1

Below are the measures I developed for the __Measures table: 

AVERAGE PAYMENT OF PAID ENCOUNTER FISCAL YEAR LESS ONE MONTH = CALCULATE(DIVIDE([TOTAL PAYMENT FISCAL YEAR MINUS ONE MONTH], [NUMBER OF PAID VALID PATIENT VISIT FISCAL YEAR MINUS ONE MONTH]))

PERCENT OF PAID ENCOUNTER MINUS ONE MONTH = calculate(DIVIDE(value([NUMBER OF PAID VALID PATIENT VISIT FISCAL YEAR MINUS ONE MONTH]),value([NUMBER OF ALL PATIENT ENCOUNTERS MINUS ONE MONTH])))

REVENUE PROJECTION = [DISTINCT PAT NUM FOR REV PRO]*[PERCENT OF PAID ENCOUNTER MINUS ONE MONTH]*[AVERAGE PAYMENT OF PAID ENCOUNTER FISCAL YEAR LESS ONE MONTH]

DISTINCT PAT NUM FOR REV PRO = CALCULATE(DISTINCTCOUNT('Table1'[visit_id]),FILTER('Table1','Table1'[bill_status] = "billed" || [BILL_STATUS] = "CLOSED" ), FILTER('Table1', [fiscal_month]='Jul" || [fiscal_month]="Aug" || [fiscal_month]="Sep" || [fiscal_month] = "Oct" || [fiscal_month]="Nov" || [fiscal_month] ="Dec" || [fiscal_month]="Jan" || [fiscal_month]= "Feb" || [fiscal_month]="09-Mar" || [fiscal_month]="Apr" || [fiscal_month]= "May" || [fiscal_month]="Jun"))

AVERAGE PAYMENT OF PAID ENCOUNTER FISCAL YEAR LESS ONE MONTH = CALCULATE(DIVIDE([TOTAL PAYMENT FISCAL YEAR MINUS ONE MONTH], [NUMBER OF PAID VALID PATIENT VISIT FISCAL YEAR MINUS ONE MONTH]))

TOTAL PAYMENT FISCAL YEAR MINUS ONE MONTH (not including the month of June): 

CALCULATE(SUMX('Table1','Table1'[total_payment]), FILTER('Table1','table1'[fiscal_month]<>"Jun"), FILTER('Table1','Table1'[total_payment]<>0), FILTER(__Measures,[DISTINCT PATIENT ENCOUNTERS]<>0))

DISTINCT PATIENT ENCOUNTERS = CALCULATE(DISTINCTCOUNT('Table1'[visit_id]),FILTER('Table1','Table1'[visit_id] <> 9999))

NUMBER OF PAID VALID PATIENT VISIT FISCAL YEAR MINUS ONE MONTH = CALCULATE([DISTINCT PATIENT ENCOUNTERS],FILTER('Table1',VALUE('Table1'[total_payment]) <> 0),FILTER('Table1','Table1'[fiscal_month]<>"Jun"))

PERCENT OF PAID ENCOUNTER MINUS ONE MONTH = calculate(DIVIDE(value([NUMBER OF PAID VALID PATIENT VISIT FISCAL YEAR MINUS ONE MONTH]),value([NUMBER OF ALL PATIENT ENCOUNTERS MINUS ONE MONTH])))

NUMBER OF ALL PATIENT ENCOUNTERS MINUS ONE MONTH = CALCULATE(COUNT('Table1'[visit_id]),FILTER('Table1','Table1'[visit_id] <> 9999), FILTER('Table1','Table1'[fiscal_month]<>"Jun"))

NUMBER OF PAID VALID PATIENT VISIT FISCAL YEAR MINUS ONE MONTH = CALCULATE([DISTINCT PATIENT ENCOUNTERS],FILTER('Table1',VALUE('Table1'[total-payment]) <> 0),FILTER('Table1','Table1'[fiscal_month]<>"Jun"))

AVERAGE PAYMENT OF PAID ENCOUNTER FISCAL YEAR LESS ONE MONTH and PERCENT OF PAID ENCOUNTER MINUS ONE MONTH are required to be calculated based on 11 months (excluding the month of June), 

However, the revenue projection is based on [DISTINCT PATIENT VISIT](12 months of data) * [PERCENT OF PAID ENCOUNTER MINUS ONE MONTH (11 Months of data)] * [AVERAGE PAYMENT OF PAID ENCOUNTER FISCAL YEAR LESS ONE MONTH (11 months of data)]. 

The 11 months filters are built inside the variables [PERCENT OF PAID ENCOUNTER MINUS ONE MONTH] * [AVERAGE PAYMENT OF PAID ENCOUNTER FISCAL YEAR LESS ONE MONTH]. When I put them together with [DISTINCT PATIENT VISIT] in the Matrix table, the numbers are totall off. 

As you can see from the excel sheet below,  the red part is based on 11 months of data and the green part 12 months. 

July 2021 - June 2022			2021						2022							2021						2022
clinics	Percent of Encounters Paid	Average Payment of Paid Encounter	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Grand Total	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Revenue Projection through June 2022
clinicA	54%	(152.96)	9845	10100	10128	10315	9743	9363	9295	9119	10714	9409	9098	8425	115554	(818,975)	(847,103)	(847,887)	(839,692)	(813,836)	(769,010)	(753,252)	(747,752)	(943,782)	(840,728)	(845,986)	(782,707)	########
clinicB	77%	(188.68)	1034	1077	1048	977	1006	1076	1109	964	1180	1009	1017	998	12495	(168,506)	(175,640)	(169,991)	(165,436)	(166,518)	(176,909)	(188,557)	(163,066)	(196,443)	(169,781)	(176,796)	(164,670)	########
clinicC	66%	(314.46)	8720	9196	9084	8915	8585	7847	8429	8181	9675	8595	8818	7248	103293	(2,011,458)	########	########	########	########	########	########	########	########	########	########	########	########
clinicD	50%	(356.22)	12826	13845	13562	12928	12669	12240	12575	12104	14229	12826	13307	11574	154685	(2,914,662)	########	########	########	########	########	########	########	########	########	########	########	########
Grand Total		32425	34218	33822	33135	32003	30526	31408	30368	35798	31839	32240	28245	386027	(5,913,600)	########	########	########	########	########	########	########	########	########	########	########	########

Thank you.  Ev.

[v-xiaotang]

Hi @Anonymous 

Thanks for reaching out to us.

>>  AVERAGE PAYMENT OF PAID ENCOUNTER FISCAL YEAR LESS ONE MONTH and PERCENT OF PAID ENCOUNTER MINUS ONE MONTH are required to be calculated based on 11 months (excluding the month of June), 

you need to filter table by date range, usually we can use edate() or eomonth() function to get the date several months ago, and then set a date range to filter the table. Honestly, it is not very clear what you are trying to calculate, so I cannot write an accurate measure for it right now... Maybe you can decompose the overall problem into several smaller, more easily solved problems...

Best Regards,

Community Support Team _Tang

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

[Anonymous]

Hi @v-xiaotang , thanks for responding to the post.  Let me try another way to simplify my question. 

I need to calculate the projected revenue based on (12 months of distinct patient encounter )* (11 months of Average Payment Of Paid Encounter) * (11 months of Percentage of Paid Encounter). 

I have built in filters of 11 months (as shown in the original post) into the formulas for Average Payment of Paid Encounter and Percentage of Paid Encounter.The problem is that I cannot get to the right projected revenue amount when I multiply these 3 variables.  Is there a way to keep Average Payment of Paid Encounter and Percentage of Paid Encounter static (not affecting by filters other than the built-in ones)?  I use the page level filter to calculate the 12 months of distinct patient encounter but I think the page-level filter messes with the 2 other variables (that have a built-in 11 months filter).

Yesterday, I tried creating a separate summary table (SUMMARIZE()) for the Average Payment of Paid Encounter and Percentage of Paid Encounter. I then calculated the projected revenue based on the summary table. However, the number is also way off.

Any suggestions are appreciated.