Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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.
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.
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.
Hi,
Share input data in a format that can be pasted in an MS Excel file and show the expected resutl on that dataset.
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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |