Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
davetech007
Frequent Visitor

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

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:

1.JPG

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]))
 

2.JPG

 

The Visual with ID aggregation / day:

 

davetech007_1-1620725461580.png

 

 

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!

 

3.jpg

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

5 REPLIES 5
davetech007
Frequent Visitor

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
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

davetech007_2-1620976843995.png

And with drilldown it is visible, that for example in this month was the 2 issues "in progress" every day! 

davetech007_3-1620976869564.png

 

 

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:

davetech007_0-1620976489579.png

IssueInArbeit (generated new table):

davetech007_1-1620976535648.png

 

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.

yingyinr_0-1623233560927.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

New2.JPG

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 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.