Calculate Earned income when no finance column exist

[Anonymous]

As a non-profit, we offer programs where grant funding only pays when a person finishes (attends) a full session. A 90 day treatment program may entail 60 mandatory therapy sessions. For every therapy seesion a patient attends, we are paid $131.00. The patient name is logged daily as either:

Attended Billable     $131.00

Late                         $  35.00

Left Session Early    $  55.00

Did not attend        $  00.00

I hope to create a measure that will counts the patients during a set time frame with the Billable status to see how much money was earned.

For example, I have  three patients that attends 12 sessions this week. I want to count the sessions, then multiply those sessions by the funding fee:

12 x 131 = 1,572.00

[Anonymous · Accepted solution]

I resolved the issue by creating a new column called bill rate and used the IF statement to set guidelines. The created a measure to display the calculation:

Bill Rate =

IF(Rosters[Attendance Status] = "AB=Attended Billable", "$131.56", "$00.00")

Total Earnings =

SUM(Rosters[Bill Rate])

[Anonymous]

Allison,

My primary table is called Roster, I also have a calendar table and a Measures table which is linked by a Patient ID. Below is a picture of the starter data being requested. I don't think a sample dataset is necessary considering I am seeking a measure that will look in the Roster table, for the Attendance Status column where AB=Attended Billable is multiplied by $131.21. So in this pic, the result will be 131.21 x 2 = Total Earnings of $262.42

[AllisonKennedy]

@Anonymous This is making sense so far and is very possible, but we need to know what your raw data looks like. 

Do you have an attendance record table and a table of Patients with unique IDs? Please make some sample data that has about 3 patients and their attendance over 2 weeks. Paste the data directly from Excel into a post here so we can copy it easily into Power BI, or upload to OneDrive and share.

Thanks!