Forum Discussion
Getting/generating all dates (days) between multiple dates per ID
Hi Anonymous ,
Thank you for your fast reply!
I have shared the 2 .csv files, how it should be:
https://drive.google.com/drive/folders/1Oe9n4BEJ1oZ5MD_ZrwCIYuGKzxDP-O6d?usp=sharing
StatusWechsel.csv is the original table, and from it i want to generate a new table with the "issueid" and the (generated) days, when it was "Work in Progress" (IssueInArbeit.csv).
It is strange and seems like not so efficient to generate so many rows (every row should be a new day, when the ID "in progress") in a table, but at least it is working. 🙂 As long as i know, it is only possible to visualize continously the days, it it as existing table/column somewhere, and there isn't an easier way (measure?) for it?
Expected result a visual with:
- X-Axis the date "IssueInArbeit" from the generated dates in the new table
- The values should be the issues - distinct counted every ID, when it was "in progress".
That means, if for example from MAY 2020 - SEP 2020 was only 1, after that was already 2 issues "in progress" in every month. Of course these issues should be counted until they have a specific status (which includes "closed", when they are finally closed too), in this example they are still in progress.
And with drilldown it is visible, that for example in this month was the 2 issues "in progress" every day!
The Conditions (i want to consider only the status, nothing else):
- StatusWechsel[NewStatus] NOT IN {"Open", "Neu", "Selected for Development", "Geprüft", "Closed", "Keine Umsetzung"}
That means, if the issue leaves any of this status anytime (for example at 01.01.2021), should be counted as "Work in Progress" at that day ("Work in Progress" from -> 01.01.2021) already based on the [Transition_Date].
But after that, if it has any of this NOT wanted [NewStatus], for example "Open" at 05.01.2021, it should be "Work in Progress" till 05.01.2021 (incl. this day)
StatusWechsel:
IssueInArbeit (generated new table):
I hope it is clear, what i want to achieve! When not, i am ready to reply! 🙂
Thank you for your help again!
Best Regards,
David
Hi davetech007 ,
You can refer the content in the following links to get it after you get the right date range:
Fill dates between dates with Power BI / Power Query
I created a sample pbix file(see attachment), please check whether that is what you want.
Best Regards
Hi Anonymous !
Thank you for your answer!
Thank you, i have checked your solution, and it is really optimized and clear, but:
- it has the same result, which i already have: dates between 1 min and 1 maximum date per ID. What i really want is, to always consider the columns "NewStatus" too.
 
Like in this screenshot, the dates should be generated between:
- from the first date, when "NewStatus" leaves any of "Open"; "Selected for Development"; "Closed"
- until "NewStatus" has a value again, "Open" or "Selected for Development" or "Closed". That means, these dates should be then NOT generated
- IF the last "NewStatus" last value not equals to "Closed", the last (MAX) date should be generated until today, because it means it is still not a closed issue!
For example for ID 481721 should be (in purple the considered values):
- 23.03.2020 till 02.06.2020 (after "NewStatus" leaves Open, until Open again!)
- it starts again from 12.10.2020 till 04.11.2020
- 02.12.2020 - 03.01.2021
- 17.02.2021 - 26.02.2021 (after it was closed, somebody opened this issue again)
For ID 559737:
- 08.03.2021 - 22.03.2021
- 24.03.2021 - TODAY() (because the latest "NewStatus" not equals "Closed")
I hope it is clear! And i hope, that you could help me to solve this case!
Thank you very much in advance!
Best Regards,
David