Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RemiAnthonise
Helper V
Helper V

Calculate amount 'still have to be paid'

Hi guys,

 

I have a little problem finishing my report. The final column / measure I have to create is 'remainder end of contract'. 

We have studycosts for our employees. For example, someone follows an education for 4 years. This education costs 4000 Euro, so it's 1000 Euro a year. After the second year this employee stops working for us so there are 2 years left, this makes 2000 Euro left. 

 

I 've made the following measure:

remainder end of contract =
IF('Employees'[EmploymentEndDate] < 31-12-2154;
('ACKStudies'[final paydate] - 'Employees'[EmploymentEndDate]) * 'ACKStudies'[Costs per day];
SUM('ACKStudies'[Still have to be paid]))

 

[EmploymentEndDate] is the enddate if someone stops working for us. When this field is empty it gives me 31-12-2154. So if the value for this field < 31-12-2154 the date is filled so the employment is ended.

'ACKStudies'[final paydate] is the date the 'studycost' is paid. For example, an education lasts 4 years and the invoicedate is 1-1-2018, the final paydate is 31-12-2021. 

Employees'[EmploymentEndDate] is the enddate I've explained above. Final paydate - EmploymentEndDate gives me the number of days between last day of payment en the employmentenddate. I know the costs per day so I multiply this with these days. 

If the Employees'[EmploymentEndDate] == 31-12-2154 I want to see SUM('ACKStudies'[Still have to be paid]).

 

It is almost working, the problem is ( I  think... ) this:
('ACKStudies'[final paydate] - 'Employees'[EmploymentEndDate]) * 'ACKStudies'[Costs per day]; 

 

I've attached a sample file, you'll see the error in the ACKStudies. I hope it's clear for you guys, if not please let me know.

https://www.dropbox.com/s/33mhu6wk0hk03py/Studycost%20example.pbix?dl=0 

2 ACCEPTED SOLUTIONS
v-juanli-msft
Community Support
Community Support

Hi @RemiAnthonise

The formula:

IF('Employees'[EmploymentEndDate] < 31-12-2154

could be replaced with the following

Measure1 = var datetexttype=FORMAT(MAX(Employees[EmploymentEndDate]),"dd/mm/yyyy") 
var specificdate=FORMAT(DATE(2154,12,31),"dd/mm/yyyy")
return IF(datetexttype <>specificdate,1,0)

it would distinguish between 2154/12/31 and other dates.

5.png 

 

then i create another measure independently showing the formula:

('ACKStudies'[final paydate] - 'Employees'[EmploymentEndDate]) * 'ACKStudies'[Costs per day]

Measure3 = MAX('ACKStudies'[final paydate]) - MAX('Employees'[EmploymentEndDate]) * MAX('ACKStudies'[Costs per day])

But it shows error

 

When i look into your dataset, 'ACKStudies'[final paydate] is a column with date type, 'Employees'[EmploymentEndDate]) is also a date type column, 'ACKStudies'[Costs per day] is a column with number type, it is incorrect to use date type column in the calculation.

Please let me know what this formula you mean so that i can figure out the alternatives.

 

Best Regards

Maggie

 

 

 

View solution in original post

Hi Maggie @v-juanli-msft

I've applied your steps and ( I think... ) I've finished it, see below.

https://www.dropbox.com/s/33mhu6wk0hk03py/Studycost%20example.pbix?dl=0 

 

You've created Measure1, there already was the column 'Still have to be paid' so now there is Measure3:

 

Measure3 =
IF([Measure1] = 1;
(MAX('ACKStudies'[final paydate]) - MAX('Employees'[EmploymentEndDate])) * MAX('ACKStudies'[Costs per day]); MAX(ACKStudies[Still have to be paid]))

 

I think this is it. If you're willing to check it, please.

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

Hi @RemiAnthonise

The formula:

IF('Employees'[EmploymentEndDate] < 31-12-2154

could be replaced with the following

Measure1 = var datetexttype=FORMAT(MAX(Employees[EmploymentEndDate]),"dd/mm/yyyy") 
var specificdate=FORMAT(DATE(2154,12,31),"dd/mm/yyyy")
return IF(datetexttype <>specificdate,1,0)

it would distinguish between 2154/12/31 and other dates.

5.png 

 

then i create another measure independently showing the formula:

('ACKStudies'[final paydate] - 'Employees'[EmploymentEndDate]) * 'ACKStudies'[Costs per day]

Measure3 = MAX('ACKStudies'[final paydate]) - MAX('Employees'[EmploymentEndDate]) * MAX('ACKStudies'[Costs per day])

But it shows error

 

When i look into your dataset, 'ACKStudies'[final paydate] is a column with date type, 'Employees'[EmploymentEndDate]) is also a date type column, 'ACKStudies'[Costs per day] is a column with number type, it is incorrect to use date type column in the calculation.

Please let me know what this formula you mean so that i can figure out the alternatives.

 

Best Regards

Maggie

 

 

 

Hi Maggie @v-juanli-msft

I've applied your steps and ( I think... ) I've finished it, see below.

https://www.dropbox.com/s/33mhu6wk0hk03py/Studycost%20example.pbix?dl=0 

 

You've created Measure1, there already was the column 'Still have to be paid' so now there is Measure3:

 

Measure3 =
IF([Measure1] = 1;
(MAX('ACKStudies'[final paydate]) - MAX('Employees'[EmploymentEndDate])) * MAX('ACKStudies'[Costs per day]); MAX(ACKStudies[Still have to be paid]))

 

I think this is it. If you're willing to check it, please.

Hi @RemiAnthonise

Yes, your are right.

Measure3 can replace MAX(ACKStudies[Still have to be paid]) to SUM(ACKStudies[Still have to be paid]) , since your table only have one row for each [Still have to be paid], MAX or SUM is OK anyway.

 

By the way, this problem is sloved, right?

 

Best Regards

maggie 

@v-juanli-msft Yes, problem is solved. Thanks a lot!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.