Forum Discussion
Not Started, Completed, In Progress based on Dates
Hi,
I am trying to give status to my progect activities based on Slicer Date:
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
7 Replies
Anonymous
Measure = VAR slicer = [SELECTED VALUE] return IF(MAX('Activities'[PlannedStart]) < slicer,"Not Started", IF(slicer > MAX('Activities'[PlannedFinish]), "Completed" , "In Progress"))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
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.
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
Anonymous You need to use some aggregated function with that like MAX etc.
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
