Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
That was my problem.
I have a table with projects where one of the columns indicates in which phase this project is.
I also have columns indicating each phase of the project. Where I put the date when the project goes into that phase.
I would like to create a measure using DATEDIFF(Date1;Today();Day) to see how long does each project are in the phase.
But how do I make Date1 be the column corresponding to the phase the project is in?
Project | Stage | Idea | Business Case | Development | Launch |
P1 | Launch | 01/02/2019 | 01/03/2019 | 01/04/2019 | 01/05/2019 |
P2 | Idea | 01/02/2019 | |||
P3 | Business Case | 01/02/2019 | 01/03/2019 | ||
P4 | Development | 01/02/2019 | 01/03/2019 | 01/04/2019 | |
P5 | Launch | 01/02/2019 | 01/03/2019 | 01/05/2019 | |
P6 | Launch | 01/02/2019 | 01/04/2019 | 01/05/2019 | |
P7 | Development | 01/02/2019 | 01/04/2019 | ||
P8 | Launch | 01/02/2019 | 01/05/2019 |
Example: Today is 05/06/2019
If I select P2; I want to see that P2 is on the Idea phase for 124 days.
if I select P3; I want to see that P3 is on the Business Case phase for 96 days.
Any Ideas?
Solved! Go to Solution.
hi, @Anonymous
Just try this formula to create a column:
NewDaysInStage = IF ( Table1[LatestStage] = Table1[Stage], DATEDIFF ( Table1[StageDate], TODAY () , DAY ), DATEDIFF ( Table1[StageDate], CALCULATE ( MIN ( Table1[StageDate] ), FILTER ( Table1, Table1[Project] = EARLIER ( Table1[Project] ) && Table1[StageDate] > EARLIER ( Table1[StageDate] ) ) ), DAY ) )
Best Regards,
Lin
How does your data model look like? Is it 3 columns (Project Id, Stage, Date) or is it multiple columns (1 for date per stage)? Can you post a sample of your source data?
Also, take a look at the unpivoting feature in Power BI to convert columns to rows.
https://docs.microsoft.com/en-us/power-bi/desktop-common-query-tasks#pivot-columns
https://radacad.com/pivot-and-unpivot-with-power-bi
Thanks for answering me @tarunsingla
The database is the same as this table, but with more columns. I have one line per project, which over time I update the status.
But I still can not see how pivoting the table can help me .
See this unpivot example pbix file to understand how unpivoting can help you here.
Regards,
Tarun
Tnks for the help @tarunsingla
You were right about unpivoting. But I seeing another problem now.
Take P4 in your example.
Idea - 02/01/2019
Bussiness Case - 03/01/2019
Development - 04/01/2019
hi, @Anonymous
What is your expected output of P4?
Why it is only 1 day? Can you show the expected output in different scenario?
Best Regards,
Lin
Using P4 as an example.
Today() = 11-jun-19.
I would like to see that P4 is in Development Stage, since 01-apr-19. So, P4 is in Development for 71 days. (Today()) - (01-apr-19)
But, P4 took 28 days to leave the Idea Stage. (01-mar-19) - (01-feb-19)
And 31 days to leave the Business Case Stage. (01-apr-19) - (01-mar-19)
Best Regards,
hi, @Anonymous
Just try this formula to create a column:
NewDaysInStage = IF ( Table1[LatestStage] = Table1[Stage], DATEDIFF ( Table1[StageDate], TODAY () , DAY ), DATEDIFF ( Table1[StageDate], CALCULATE ( MIN ( Table1[StageDate] ), FILTER ( Table1, Table1[Project] = EARLIER ( Table1[Project] ) && Table1[StageDate] > EARLIER ( Table1[StageDate] ) ) ), DAY ) )
Best Regards,
Lin
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |