DAX to create a Graphic (DAILY WIP) with Jira Software Database (help please)

[pedrohenriquewe]

Hello everyone,

Btw, I do apreciate your help for trying to solve my problem. 

All dates are in format: DD/MM/YYYY
The columns in the same colour are the relashionship between tables.

My Data (3 tables):

Table: History (fact)
key	Status	Status Date
X-1	Backlog	01/01/2023
X-1	In Progress	02/01/2023
X-1	Ready to Release	05/01/2023
X-1	Done	06/01/2023
X-2	Backlog	03/01/2023
X-2	In Progress	04/01/2023
X-2	Ready to Release	10/01/2023
X-2	Done	11/01/2023
X-3	Backlog	05/01/2023
X-3	In Progress	07/01/2023

Table: Issues (dimension)
key	Current Status	Resolved Date
X-1	Done	06/01/2023
X-2	Done	11/01/2023
X-3	In Progress

* Colunm key is the primary key

Dcalendar
Date	Month	Year
01/01/2023	1	2023
02/01/2023	1	2023
03/01/2023	1	2023
04/01/2023	1	2023
05/01/2023	1	2023
06/01/2023	1	2023
07/01/2023	1	2023
08/01/2023	1	2023
09/01/2023	1	2023
10/01/2023	1	2023
11/01/2023	1	2023
12/01/2023	1	2023

OUPUT NEEDED:
I need to create a graphic that shows my Daily WIP (work in progress). Like this (I just need the visual, the table beside is just the data that genated it - but it would be a 'nice to have' to know all Issues in each day as a tooltip for example)


Daily WIP is the number of Issues (every Issue is a 'key') that was being doing in that day. It will be cosider "being doing" all statuses diferent from Done and Backlog ('In Progress', 'Ready to Release'). The table above shows the Issues that are considered in each Day, for example:
The WIP, for Issue X-1, starts at '02/01/2023' (it was moved to 'in progress' status at 02/01/23) and ends at 05/01/2023 (it was moved to 'Done' the next day).

[AlanFredes · Accepted solution]

Hi Pedro,

I had to merge the History table with a filter Issue table (Status=Done) to get the resolved date for each key. I did this in the Power Query editor.
This helped me create a DAX measure that could filter this New_History table and get the result you wanted.

Below is a link to the file I created with this new table and DAX measure.
Let me know if the join is not possible to do in your data due to too many rows and we can keep troubleshooting the issue.

Link to File: DAX to create a Graphic (DAILY WIP) with Jira Software Database (help please).pbix

Did I answer your question? Mark my post as a solution!

[pedrohenriquewe]

Wow! What a smart way to solve this problem.

However, I'm afraid this join could get me in trouble since my History table will have something like 1-2 millions rows... what do you think about that?

Could I remove the join and use 
"LOOKUPVALUE(Issues[Resolved Date], Issues[Key], 'New History'[Main_Key])" In place of (Issues.Resolved. Date) ?

It seems to work but i'd like double check with you... do you have any disclaimer about that? 

and again, thanks for helping me. 

New measure:

WIP Count =

VAR Now_Date = MAX(Dcalendar[Date])

RETURN

CALCULATE(

    DISTINCTCOUNTNOBLANK('New History'[Main_Key]),

    FILTER(

        'New History',

        'New History'[Status]<>"Backlog" && 'New History'[Status Date]<=Now_Date && OR(LOOKUPVALUE(Issues[Resolved Date], Issues[Key], 'New History'[Main_Key])>Now_Date,LOOKUPVALUE(Issues[Resolved Date], Issues[Key], 'New History'[Main_Key])=BLANK())

    )

)

[AlanFredes]

Hi Pedro,

LOOKUPVALUE would work. NATRUALLEFTJOIN was also an option but brings in all the columns from the Issue table.
Note that by doing the join in DAX it will eat up visualization memory. which in Power BI premium is 6GB in P1 node (Link). You shouldn't have any issues as long as you are not pulling in too much data at the same time into the visualization. If you are just looking at 1 month at a time you should be good but if you start pulling in years of data you might see that the visualization will take time to render and if the worst case throw an out-of-memory error. Note that if your computer has more than 6GB of memory you will have no issues but once you publish it you will get a memory error in Power BI Service. I learned this the hard way.