Getting/generating all dates (days) between multiple dates per ID

[davetech007]

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:

• date InProgress, when it is leaving any of these Status: 

IssueInArbeit(WiP1) =

IF(NOT(StatusWechsel[NewStatus] IN {"Open"; "Neu"; "Selected for Development"; "Geprüft"});

StatusWechsel[Transition_Date])

• inArbeitVon (inProgressFrom), getting the first date from the column above:

inArbeitVon(WiP2) =

VAR inArbeitVon = IF(StatusWechsel[IssueInArbeit(WiP1)] = CALCULATE(MIN(StatusWechsel[IssueInArbeit(WiP1)]); ALLEXCEPT(StatusWechsel; StatusWechsel[issueid]));1)

RETURN

IF(inArbeitVon = 1; StatusWechsel[IssueInArbeit(WiP1)])

• inArbeitBis (inProgressTill), here i am checking, if it is still in progress, then get the date TODAY, else last date:

inArbeitBis(WiP3) =

VAR inArbeitBis = IF(StatusWechsel[IssueInArbeit(WiP1)] = CALCULATE(MAX(StatusWechsel[IssueInArbeit(WiP1)]); ALLEXCEPT(StatusWechsel; StatusWechsel[issueid]));1)

Var IsClosed = IF(StatusWechsel[IssueInArbeit(WiP1)] = CALCULATE(MAX(StatusWechsel[IssueInArbeit(WiP1)]); FILTER(ALLEXCEPT(StatusWechsel; StatusWechsel[issueid]; StatusWechsel[NewStatus]); StatusWechsel[NewStatus] IN {"Closed"; "Keine Umsetzung"}));1)

RETURN

IF(inArbeitBis = 1 && IsClosed <> 1; TODAY();

IF(inArbeitBis = 1; StatusWechsel[IssueInArbeit(WiP1)]))

It is working correctly, i am getting these 2 dates:

 

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!

IssueInArbeit =

CALCULATETABLE(SELECTCOLUMNS(GENERATE(StatusWechsel;

FILTER(CALENDAR(

CALCULATE(MIN(StatusWechsel[inArbeitVon(WiP2)]); ALLEXCEPT(StatusWechsel; StatusWechsel[issueid]));

CALCULATE(MAX(StatusWechsel[inArbeitBis(WiP3)]); ALLEXCEPT(StatusWechsel; StatusWechsel[issueid])));

[Date] >= StatusWechsel[inArbeitVon(WiP2)] && [Date] <= StatusWechsel[inArbeitBis(WiP3)]));

"IssueID"; StatusWechsel[issueid]; "IssueInArbeit"; [Date]; "IssueType"; StatusWechsel[JiraIssueDetails.JI_Issuetype]))

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:

TESTisInArbeitOrClosed =

Var IsInArbeit = IF(NOT(StatusWechsel[NewStatus] IN {"Open"; "Neu"; "Selected for Development"; "Geprüft"; "Closed"; "Keine Umsetzung"});1)

RETURN

IF(IsInArbeit = 1; "InArbeit"; "IsClosed")

TESTinArbeitVon =

VAR inArbeitVon = IF(StatusWechsel[TESTisInArbeitOrClosed] = "InArbeit"; 1)

RETURN

IF(inArbeitVon = 1; StatusWechsel[Transition_Date])

TESTisClosedVon =

VAR inArbeitBis = IF(StatusWechsel[TESTisInArbeitOrClosed] = "IsClosed"; 1)

VAR maxDate = IF(StatusWechsel[Transition_Date] = CALCULATE(MAX(StatusWechsel[Transition_Date]); ALLEXCEPT(StatusWechsel; StatusWechsel[issueid])); 1)

VAR IsClosed = IF(StatusWechsel[Transition_Date] = CALCULATE(MAX(StatusWechsel[Transition_Date]); FILTER(ALLEXCEPT(StatusWechsel; StatusWechsel[issueid]; StatusWechsel[TESTisInArbeitOrClosed]);

StatusWechsel[TESTisInArbeitOrClosed] = "InArbeit"));1)

RETURN

IF(IsClosed = 1 && maxDate = 1; TODAY();

IF(inArbeitBis = 1; StatusWechsel[Transition_Date]))

Now i get multiple dates, where it was in progress, for example :

• in progress from 12.10.2020 -> is closed from 08.03.2021
• then again in progress from 08.03.2021 -> is closed again from 22.03.2021
• in progress from 24.03.2021 -> till TODAY

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

[davetech007]

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

[v-yiruan-msft]

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

• StatusWechsel[NewStatus] not IN {"Open"; "Neu"; "Selected for Development"; "Geprüft"}
• StatusWechsel[Transition_Date] > xxx
• xxxx

3. Expected result (the setting of column chart: Axis: xxx Values:xxx)

    Number of WIP days=xx+xxx+xx

Best Regards

[davetech007]

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:

• 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

[v-yiruan-msft]

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

[davetech007]

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:

• 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