Not Started, Completed, In Progress based on Dates

[Anonymous]

Hi,

I am trying to give status to my progect activities based on Slicer Date:

Measure =

VAR slicer = [SELECTED VALUE]

return

IF('Activities'[PlannedStart] < slicer,"Not Started", IF(slicer > 'Activities'[PlannedFinish], "Completed" , "In Progress"))

But the IF Condition is not taking the Start and Finish Date, it is giving error

[Anonymous · Accepted solution]

Hi  @Anonymous ,

The Max()/Min() functions express different meanings in calculated and measure, for example:

1. Using the Max() function in calculated is to take the maximum value of this column, see the result below

max_measure = MAX('Activity'[planned finish])

[planned finish] The largest date in the column is 2024.10.9

2. Use the max() function in measure to take the value of the current row.

max_measure = MAX('Activity'[planned finish])

Take out the current value of each row

So use the following function according to your rules, you can compare the value in the slicer with each Activity in the table row by row and compare the start and finish dates of each activity

Measure 2 =
var _select =SELECTEDVALUE('Table'[Date])
return
IF(MAX('Activity'[planned start]) <_select,"Not Started", IF(_select > MAX('Activity'[planned finish]), "Completed" , "In Progress"))

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

This gave me a partial solution. and Thank you for explaining the concept as well

[Tahreem24]

@Anonymous You need to use some aggregated function with that like MAX etc.

[TomMartens]

Hey @Anonymous ,

the challenge you are facing has a name - Events-in-progress.

This article

Events-In-Progress | Gerhard Brueckl on BI & Data (gbrueckl.at)

contains all the necessary links to get started and also to create solutions for more advanced scenarios. A good start is the article by Jason Thomas.

Hopefully, this provides some more insights.

Regards,

Tom

[Greg_Deckler]

@Anonymous 

Measure =
VAR slicer = [SELECTED VALUE]
return
IF(MAX('Activities'[PlannedStart]) < slicer,"Not Started", IF(slicer > MAX('Activities'[PlannedFinish]), "Completed" , "In Progress"))

[Anonymous]

@Greg_Deckler , I did try the Min and Max but it is not giving correct answer, as we want to go row by row for every activity in the table and compare the start finish date for each, and not pick Max or Min date from the Column

[Greg_Deckler]

@Anonymous If you have each event in a table visual then the MAX and MIN will give you the value for that event (it is the max or min of 1). If you are doing something different than that, then would need that detail.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.