Create new DAX calculation for denominator

[Anonymous]

PROBLEM: Replace denominator which calculates the number of staff that touched a member in a specific program in a particular month with a manual table created that has a static number of FTEs for each program

The challenge the team has is that they use part time individuals when permanent members are out of the office etc and so evethough a program has 5 FTEs, the number in the current denominator can be 6 of 7 one month because the part time people show up in the data.  The static table will instead be needed as the denominator.

Current Dax to calculate: CM Avg Case Load = DIVIDE([CM Ongoing Care Plans],[CM Case Manager Count]) – GOAL IS TO REPLACE CM Case Manager Count with new table

---------------------

Numerator: Ongoing Care Plans (# in month of managed people)

CM Ongoing Care Plans = [CM Cummulative Distinct Count]-[CM Terminations Until Previous Month]

Cummulative Distinct:

CM Cummulative Distinct Count =

IF(

    ISBLANK([CM Referrals]),

    BLANK(),

CALCULATE(

    DISTINCTCOUNT('MHK Program'[Program_No]),

    DATESBETWEEN(

        d_Date[CalendarDate],

        DATE("2019","12","01"),

        MAX(d_Date[CalendarDate])),

        'MHK Program'[Enrolled]=1))

CM Terminations Until Previous Month =

IF(

    ISBLANK([CM Referrals]),

    BLANK(),

CALCULATE(

    DISTINCTCOUNT('MHK Program'[Program_No]),

    USERELATIONSHIP(d_Date[CalendarDate],'MHK Program'[Case Load End Date]),

    DATESBETWEEN(

        d_Date[CalendarDate],

        DATE("2019","12","01"),

        MAX(d_Date[CalendarDate])),

        'MHK Program'[Enrolled]=1,

// OR(

    'MHK Program'[Case Load End Date]<=EOMONTH(MAX(d_Date[CalendarDate]),-1)))

--------------------------------------------

Denominator: Case Manager Count

Case Manager Count =

VAR CaseManager = 'MHK Program'[Case Manager Name Care Plan]

VAR ReferralMonthStart = EOMONTH('MHK Program'[Date_Received],-1) + 1

VAR ReferralEndMonth = EOMONTH('MHK Program'[Date_Received],0)

VAR Program = 'MHK Program'[ProgramOriginal]

RETURN

   IF(CALCULATE (

        DISTINCTCOUNT('MHK Program'[Program_No]),

        FILTER (

            'MHK Program',

            'MHK Program'[Case Manager Name Care Plan] = CaseManager

             && 'MHK Program'[ProgramOriginal] = Program

             && 'MHK Program'[Enrolled] = 1

             && 'MHK Program'[Date_Received] >= ReferralMonthStart

             && 'MHK Program'[Date_Received] <= ReferralEndMonth

        ))>=1,CaseManager,BLANK())

New table to replace denominator.

[Anonymous]

Awesome - thank you!  Will give this a try.

[amitchandak]

@Anonymous , if the date table is not joined then you can have two measures like

if date table is joined first measure will need crossfilter , in some case second might need that too

Case Managers =

VAR CaseManager = values('MHK Program'[Case Manager Name Care Plan])
VAR ReferralMonthStart = EOMONTH('Date'[Date],-1) + 1
VAR ReferralEndMonth = EOMONTH('Date'[Date],0)
VAR Program = values('MHK Program'[ProgramOriginal])
RETURN
CALCULATE (

DISTINCTCOUNT('MHK Program'[Program_No]),
FILTER (
'MHK Program',
'MHK Program'[Case Manager Name Care Plan] in CaseManager
&& 'MHK Program'[ProgramOriginal] in Program
&& 'MHK Program'[Enrolled] = 1
&& 'MHK Program'[Date_Received] >= ReferralMonthStart
&& 'MHK Program'[Date_Received] <= ReferralEndMonth

))

Case manager count

Countx( values('MHK Program'[Program_No]), if([Case Managers]>=1,CaseManager,BLANK()))

refer for crossfilter

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970