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.
Hi all,
I am looking for an approach / idea for the following problem:
Step1: If the contract has no end date then i want to calculate the contract time until end of year in columns (31.12.XXXX). If the contract has an end date i want to take the enddate. But if we are in 2019 and the contract ends in 2020 we take the end of year 2019 of course. I had somehow problems to bring years in the columns and consider them in a measure.
Step2: Same like Step1 but average over contract.
Some ideas for a approch?
Many thanks.
Solved! Go to Solution.
Hi @Pikachu-Power ,
Try formula like below:
result =
IF (
'Table'[End] <> BLANK (),
'Table'[End],
IF (
YEAR ( 'Table'[Start] ) = 2019
&& YEAR ( 'Table'[End] ) = 2020,
DATE ( 2019, 12, 31 ),
DATE ( YEAR ( 'Table'[Start] ), 12, 31 )
)
)
2018_ =
AVERAGEX (
FILTER ( ALL ( 'Table' ), 'Table'[Contact] = MAX ( 'Table'[Contact] ) ),
CALCULATE ( SUM ( 'Table'[2018] ) )
)
2019_ =
AVERAGEX (
FILTER ( ALL ( 'Table' ), 'Table'[Contact] = MAX ( 'Table'[Contact] ) ),
CALCULATE ( SUM ( 'Table'[2019] ) )
)
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickl
Hi @Pikachu-Power ,
Try formula like below:
result =
IF (
'Table'[End] <> BLANK (),
'Table'[End],
IF (
YEAR ( 'Table'[Start] ) = 2019
&& YEAR ( 'Table'[End] ) = 2020,
DATE ( 2019, 12, 31 ),
DATE ( YEAR ( 'Table'[Start] ), 12, 31 )
)
)
2018_ =
AVERAGEX (
FILTER ( ALL ( 'Table' ), 'Table'[Contact] = MAX ( 'Table'[Contact] ) ),
CALCULATE ( SUM ( 'Table'[2018] ) )
)
2019_ =
AVERAGEX (
FILTER ( ALL ( 'Table' ), 'Table'[Contact] = MAX ( 'Table'[Contact] ) ),
CALCULATE ( SUM ( 'Table'[2019] ) )
)
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickl
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 |
---|---|
102 | |
101 | |
78 | |
70 | |
63 |
User | Count |
---|---|
141 | |
106 | |
101 | |
85 | |
72 |