Forum Discussion

davetech007's avatar
davetech007
Frequent Visitor
5 years ago

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:

 

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

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      davetech007
      Frequent Visitor
      5 years ago

      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

       

  • davetech007's avatar
    davetech007
    Frequent Visitor
    5 years ago

    Hi Anonymous !

     

    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