Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
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.
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.
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |