Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi guys!
I would like to ask from you a question, to i unfortunately couldn't find an answer till now (after more days of searching)!
I am making a report about Jira Issues, and i would like to get the exact number of Work In Progress / day. I have already a solution, but it is not exactly, what we want. It looks like now:
I have a Table with many issues about their status change log. Every issue has a unique ID (issueid), and i am checking that ID, and generating 2 dates from it:
With these, it is easy to create the dates between MIN and MAX -> i have created a DAX table, where i get all the dates (days) between the MIN and MAX date / ID, and from this i could create a visual with rolling days, where every in every day will be this issue ID counted, where it was in progress!
The Visual with ID aggregation / day:
But the problem is:
If this issue was again in a status (for example Open, or Closed somewhere between 2 dates, where it was in progress - see the picture), should be NOT counted as in progress, should have the days again not generated. It looks like, i have to consider all the status, and from it check if it is in Progress or not. Somehow like this:
Now i could get all the dates, excl. the days, where it was not in progress (in this example from 22.03.2021 -> 24.03.2021 it was not in progress, but only 23.03.2021 should NOT be in this new table, because in 22th, and 24th somebody still worked on it!
Sorry, when this question has became too long, but it is not the easiest to explain, and it seems quite hard for me to get it done, and i am pretty sure, that some people may have the same/similuar issue and couldn't find an answer for it! 😕 🙂
I hope, that somebody could help me on it, and tell me how i should solve this! Of course, when something is not clear, i am ready to answer! 🙂
Best Regards,
David
Hi @v-yiruan-msft !
Is it understandable, what i want to achieve, or at least this should be possible to make it in Power Bi? Or should i reconsider it completely?
Thank you really in advance!
Best Regards,
David
Hi @davetech007 ,
According to the provided column formula and screenshot, it seems that what you want is to get the number of dates which status is work in progress. Could you please provide some sample data in StatusWechsel table with Text format (exclude sensitive data) and your expected result with calculation logic and samples? Thank you.
For example:
1. Sample data
StatusWechsel table
IssueID | Status_Change_ID | Transition_Date | NewStatus | Author | Project_ID | XXX |
2. Related logic/Conditions that need to be met
3. Expected result (the setting of column chart: Axis: xxx Values:xxx)
Number of WIP days=xx+xxx+xx
Best Regards
Hi @v-yiruan-msft ,
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:
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 @v-yiruan-msft !
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:
For example for ID 481721 should be (in purple the considered values):
For ID 559737:
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
93 | |
89 | |
35 | |
35 |
User | Count |
---|---|
154 | |
102 | |
82 | |
64 | |
54 |