Calculate Billings by Provider based on number of months after start date

[ryan_b_fiting]

Hello Community - 

I am in need of some help on calculating Sales for Providers, but I am looking to baseline it on the providers 1st month, 2nd month and so on.  I have a provider data table in my model that has the provider details and their start date (ehr_providers), then a billings table that has the amount billed by date (ehr_billings).

What I am looking to do is calculate is each providers billings in their 1st,2nd, 3rd.......12th month to be able to compare the providers.  So this would be a sample of the output I would expect:

Provider ID	Start Month	Month 1	Month 2	Month 3
1	1/1/2023	10,000.00	15,000.00	20,000.00
2	5/1/2023	12,000.00	19,000.00	21,000.00
3	11/1/2023	7,600.00	22,000.00

Provider 1 - Month 1 of $10k would be for January 2023, Month 2 of $15k for Feb 2023, Month 3 of $20k for March 2023

Provider 2 - Month 1 of $12k for May 2023 Month 2 of $19k for June 2023 and Month 3 of $21k for July 2023

Provider 3 - Month 1 of $7.6k for Nov 2023, Month 2 of $22k for Dec 2023 and there is nothing for Month 3 because we have the data currently stopped at December 31, 2023.

We want to compare how our providers are doing when they start and how they are growing in the roles and be able to baseline that on their number of months they have been practicing with us.

As always any help is greatly appreciated here.

Thanks

Ryan F.

[v-zhengdxu-msft · Accepted solution]

Hi @ryan_b_fiting 

Sorry, I'm not sure why only True and False are output on your side,  I'll tell you more about what I'm doing:

First, I created a table based on the data you provided:

Then create a table to store the dates for subsequent months:

Table = {"1Month","2Month","3Month"}

Then I made a modification to the previous measure:

()month = 
    VAR mon_value = SELECTEDVALUE('Table'[Value])
    VAR Newmonth = SWITCH(
        mon_value,
        "1Month", 0,
        "2Month", 1,
        "3Month", 2
    )
//Match the date to be added to the value in the table
    VAR required_Date = DATE(YEAR(MIN('Billing'[Visit Date])),MONTH(MIN('Billing'[Visit Date]))+Newmonth,DAY(MIN('Billing'[Visit Date])))
//Returns the current date
    RETURN
        CALCULATE(
            SUM('Billing'[ Billing $ ]),
            MONTH('Billing'[Visit Date]) = MONTH(
                required_Date
                )
            &&
            YEAR('Billing'[Visit Date]) = YEAR(
                required_Date
                )
        )
//The values of all billings for each month are summed and output in order of month
//Filter by year and month

Then create a matrix:

The result is as follow:

Best Regards,

Zhengdong Xu

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

[v-zhengdxu-msft]

Hi @ryan_b_fiting 

I already understand your needs:

You want to compare how well our providers are doing at the beginning and how they've grown in the role, showing data for the subsequent months of the start date;

Here is my solution:

First, I created a new table to store the dates for subsequent months:

Then create a measure:

()month =
    VAR mon_value = SELECTEDVALUE('Table'[Value])
    VAR Newmonth = SWITCH(
        mon_value,
        "1Month", 0,
        "2Month", 1,
        "3Month", 2
    )
//Match the date to be added to the value in the table
    VAR current_month = MIN('Billing'[Month])
//Returns the current date
    RETURN
        CALCULATE(
            SUM('Billing'[ Billing $ ]),
            'Billing'[Month] = current_month + Newmonth
        )
//The values of all billings for each month are summed and output in order of month

The result is as followed:

^{Best Regards,}

^{Zhengdong Xu}

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

[ryan_b_fiting]

Thanks for the reply @v-zhengdxu-msft unfortunately when I try to replicate this and tweak it for my data, it only provides me with TRUE or FALSE output.  It does not offset the months.  Unforutnately I cannot just use a month number because these will cross over years, so If they start in November, I cannot just add 3 to the current month.

I am trying to figure out another solution for this so any additional tips would be great.

[Ashish_Mathur]

Hi,

Share input data in a format that can be pasted in an MS Excel file and show the expected resutl on that dataset.

[ryan_b_fiting]

Here are some sample sets of the data:

The explanation of what we are trying to complete is in the original post:

Expected Output:

Provider ID	Start Month	Month 1	Month 2	Month 3
1	1/1/2023	10,000.00	15,000.00	20,000.00
2	5/1/2023	12,000.00	19,000.00	21,000.00
3	11/1/2023	7,600.00	22,000.00

Provider Table Sample:

Provider ID	Provider Name	Start Date
1	A	1/1/2023
2	B	5/1/2023
3	C	11/1/2023

Billing Table Sample:

Billing ID	Provider ID	Visit Date	Billing $
1	1	1/7/2023	$   2,000.00
2	1	1/20/2023	$   3,000.00
3	1	1/30/2023	$   5,000.00
4	1	2/15/2023	$   5,000.00
5	1	2/20/2023	$10,000.00
6	1	3/3/2023	$   2,000.00
7	1	3/10/2023	$   6,000.00
8	1	3/15/2023	$12,000.00
9	2	5/10/2023	$   4,500.00
10	2	5/20/2023	$   7,500.00
11	2	6/4/2023	$   3,000.00
12	2	6/9/2023	$   3,000.00
13	2	6/21/2023	$   7,000.00
14	2	6/30/2023	$   6,000.00
15	2	7/2/2023	$   9,000.00
16	2	7/15/2023	$   4,000.00
17	2	7/20/2023	$   8,000.00
18	3	11/15/2023	$   2,100.00
19	3	11/23/2023	$   5,500.00
20	3	12/12/2023	$   8,000.00
21	3	12/15/2023	$   4,500.00
22	3	12/23/2023	$   6,500.00
23	3	12/30/2023	$   3,000.00

[Ashish_Mathur]

A similar problem has been solved in the attached file.

Hope this helps.