Create Rate based on Category and Month with filters

[pbetts46]

Hello,

I am trying to calculate an "Agreement Rate" that goes by a FacilityRollup category and by the past 12 months [FinalActivityDate.Month] with filters. I have an example of the below data where you can have multiple accountid's but they will have different "Orders". I.e. there might be two of the same ID's that will have Order 1 and Order 2. I need to only include when Order = 1. I need to get the DistinctCount of "Rebilled" ActivityCodes for each FacilityRollup by month and divide it by the DistinctCount "Rebilled" and "Unbillable". I also need to only include specific "Unbillable" ActivityStatuses. Within the example I need to exclude Past Timely Filing.

This is an example of the data I am working with:

I created the formula:

Agreement Rate = DIVIDE (
     CALCULATE(DISTINCTCOUNT('Sheet1'[AccountNumber]),
     FILTER(ALLEXCEPT('All Accounts','Sheet1'[FacilityRollup],'Sheet1'[FinalActivityDate].[Month]),'Sheet1'[ActivityCode] = "REBILLED")),
     CALCULATE(DISTINCTCOUNT('Sheet1'[AccountNumber]),
     FILTER(ALLEXCEPT('Sheet1','Sheet1'[FacilityRollup],'Sheet1'[FinalActivityDate].[Month]),
'Sheet1'[ActivityCode] in {"REBILLED","UNBILLABLE"} &&
'Sheet1'[ActivityStatusCode] IN {"BILLED","REBILLED","CODECHANGEDAFTERAUDITORVERIFICATION","Administrative Decision not to Charge","BILLEDBYANOTHERENTITY","Past Timely Filing","BELOWREVENUEOPP"
})))

The output I get remains the same for every month AND I cant find a way to only include the past 12 months, so starting October, 01 2021 for instance. See below image

I am trying to get the data to look like the following:

I don't know if it's better to create individual measures or create a separate table or if my original formula just needs to be reworked. Any help is greatly appreciated! 

[v-xiaotang · Accepted solution]

Hi @pbetts46 

Thanks for reaching out to us.

In the actual results so far, the data in Matrix ranges from Aug '21 to Feb '22, but you actually want data from Apr '22 to Sep '22, right?

FYI: Solved: DAX formula to return data for last 6 months - Microsoft Power BI Community

Last6Months =
VAR TodayMonthIndex =
    CALCULATE (
        MAX ( 'Calendar'[MonthIndex] );
        FILTER ( 'Calendar'; TODAY () = 'Calendar'[DateKey] )
    )

VAR monthtocheck = Calendar[MonthIndex]

RETURN
    IF (
        AND ( monthtocheck >= TodayMonthIndex - 5; monthtocheck <= TodayMonthIndex );
        1;
        0
    )

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.