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 all, I have 2 columns importing a start date and an end date.
What I need to do is create a 'Todays target score' out of 100.
For example:
Start Date = 01/01/19
End Date = 31/12/19
Todays Target Score = 90 (example)
Any help would be great, im doing all of this
Solved! Go to Solution.
Hi @Anonymous ,
Try the following formula:
Todays Target Score =
IF (
'Table'[End Date] <> BLANK (),
IF (
DATEDIFF ( 'Table'[End Date], TODAY (), DAY ) > 0,
100,
100
- (
DATEDIFF ( TODAY (), 'Table'[End Date], DAY )
/ DATEDIFF ( 'Table'[Start Date], 'Table'[End Date], DAY )
) * 100
),
BLANK ()
)
Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Could you provide more information containing your calculation logic? Kindly share your sample data and expected result if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi @Anonymous
how does this target score should be calculated? its unclear
do not hesitate to give a kudo to useful posts and mark solutions as solution
Sorry if I wasnt clear enough, im in the process of intergrating my existing spreadsheet used to calculate a score for each project based on the start date and end date specified by the project owner. As you can see in the picture attached, if todays date is within the range itll give a score 0-100 to show todays target score. If the date has passed todays date it will show as 100 meaning the project should be complete.
Ive tried to use the below 'if' statement in a new custom column, but it fails to work. Wasnt sure if I was overcomplicating this.
Hi @Anonymous ,
Try the following formula:
Todays Target Score =
IF (
'Table'[End Date] <> BLANK (),
IF (
DATEDIFF ( 'Table'[End Date], TODAY (), DAY ) > 0,
100,
100
- (
DATEDIFF ( TODAY (), 'Table'[End Date], DAY )
/ DATEDIFF ( 'Table'[Start Date], 'Table'[End Date], DAY )
) * 100
),
BLANK ()
)
Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |