Forum Discussion
Find the Earliest Start based on a a different column condition
Hi,
I am having two linked table and ultimetly I want to find the duration time for each project depending on the location of the project (defined by the column sk_lieu)
I have a column called Quantité Réalisée describing the realised effort done.
I want first to find the earliest date registered for that column (Quantité réalisée). the column Quantité réalisé may have zéro values.
So For Each [sk_lieu] (representing a city), I want a calculated measure that gives back the earliest start date that there is a registerd value in the column [Quantité réalisé] <>0. (I can Use a slicer the [sk_lieu], so it doenst matter if the formula doesn't contain a call for the [sk_lieu].
Then I want a measure to calculate the duration up to today since that task has started
Hey Anonymous
I'm assuming that the sk_temps is the numerical version of the date. If this is the case there's a couple of ways I would approach this.
The first way I would approach this is
1.) Create a new column in your date table which is a duplicated copy of the date. Then I would change the type to whole number so now I have a representation of the date as a number (sk_temps).
2.) Disable the relationship between the Date table and Dim_Suivi_Project. Join Date to Fait_Suivi_Projects on sk_temps and sk_temps.
3.) Create this measure:
Min Date = CALCULATE(MIN('Date'[Date Range]),'Fait_Suivi_Projects'[Quantité réalisée]<>0)4.) Create this measure:
Duration = DATEDIFF([Min Date],TODAY(),DAY)(This assumes you're counting your duration by days)
This is what it ends up looking like:
If you really have to keep your joins the way they currently are in your model then keep everything the same except change your crossfilter join between Dim_Suivi_Project and Fait_Suivi_Project to both.
Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! ?
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.
1 Reply
- Watsky
Solution Sage
Hey Anonymous
I'm assuming that the sk_temps is the numerical version of the date. If this is the case there's a couple of ways I would approach this.
The first way I would approach this is
1.) Create a new column in your date table which is a duplicated copy of the date. Then I would change the type to whole number so now I have a representation of the date as a number (sk_temps).
2.) Disable the relationship between the Date table and Dim_Suivi_Project. Join Date to Fait_Suivi_Projects on sk_temps and sk_temps.
3.) Create this measure:
Min Date = CALCULATE(MIN('Date'[Date Range]),'Fait_Suivi_Projects'[Quantité réalisée]<>0)4.) Create this measure:
Duration = DATEDIFF([Min Date],TODAY(),DAY)(This assumes you're counting your duration by days)
This is what it ends up looking like:
If you really have to keep your joins the way they currently are in your model then keep everything the same except change your crossfilter join between Dim_Suivi_Project and Fait_Suivi_Project to both.
Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! ?
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.