Creating trends or snapshots of moving data?

[ALeef]

I am attempting to create a dashboard for a project lifecycle tracker.  So things are constantly in motion moving from Not Started, to Started, to Development to Testing and finally Implementation.

We track a KPI "Backlog" - basically projects that have been defined, but not started yet.  I have date stamps for created and implemented, but not started or any of the phases.  The end goal is to track the trending of the backlog, this week it is at 50, next week we start 2 more, but add 1 to the pile, so the backlog would change to 49.  I don't have any problem computing the backlog at the time of data refresh - but I'm wondering how to track it over time.

I do have a datetable that I'm using to define quarters, months, weeks etc... if I started tracking "started date" "testingdate" as well, and forced a linear progression, could I calculate backlog based on :

Todays_Backlog = Counta(Projects[ID], FILTER (AND (Projects[createdate] before today), Projects[startdate] after today)) && Project[Status] = "Approved") ?

 Obviously I messed up some syntax, but would that work like I'm thinking?  I'd probably need to keep the timestamp in, because sometimes we input, start and finish projects all in one day based on how easy they are.

Thoughts?

Can I do the same for KPI's "In Progress - Development" and "Implemented" - and/or all the other stages?  

[Greg_Deckler]

Creating that as a measure with some syntax corrections looks like it would give you what you want. You would need to use COUNTAX though and flip your parameters. If your Projects[ID] are all numbers, then I would use COUNTX instead of COUNTAX:

https://support.office.com/en-US/article/COUNTAX-Function-DAX-188a3e62-c459-414e-bdfd-3e9fb11a595d

https://support.office.com/en-US/article/COUNTX-Function-DAX-8467259a-8456-46d2-b49f-e1af6a8ab28d

[ALeef]

But, don't I want to create that as a column instead of a measure?  Creating a measure returns a singular value right?

I need to be able to graph the backlog week by week, over the year and use my datetable to drilldown.

Can I do this with a measure, or do i need the column?

[Greg_Deckler]

You could do it as a column as well, same syntax for measure and column, it is all DAX. If you wrote a meaure correctly with a date table involved, in theory you could do it as a measure and then just put it in a matrix along with your dates and it would calculate the backlog at each date. The issue with a column is going to be how your data table(s) are layed out and whether you can put the calculation in a context that makes any sense.

Really tough to say without actual sample data, table structure and desired output.

[ALeef]

 Edit: Tried inserting as code and it didn't look right

The date table is formatted like my tip/trick one from the other post.  Can you explain how I would set up the measure to be dynamic based on the date table?  All ive been able to do so far is graph projects created vs projects implemented on a drilldown cluster bar chart through the date table.

I don't have any relationships built into it - just the two tables, one fact, and one date.

As far as output, I'm just looking to be able to graph backlog over time to see that we are making progress against any new Requests that are coming in.  I'd also like to track Days to Start, Days to Develop, Variance to Target Date, Avg Time spent in testing... I'd like to drill down and have those change based on they date - so for instance see the variance to target date for 2014, and be able to drill to Q2 2014, then April 2014, and then Wk 2 April 2014.  

That way we can see if we are getting better at picking target dates, getting faster at starting projects, taking less time to test, etc.

[Greg_Deckler · Accepted solution]

So, create a new column in your Date table like this:

BackLog = COUNTAX(FILTER(requests,requests[Created Date]<[DateKey] && requests[Implement Date]>[DateKey]),[Request])

Assuming requests table is called "requests"

This will give you everything created before the date in the DateKey row but not yet implemented. You can switch up the filter to do whatever you want it to do.

Put "DateKey" from date table and "BackLog" into a table and you should have what you want, a count by day of things that have been created but not yet implemented. Obviously, you will have to tweak the filter to fit your exact requirements.

[ALeef]

Yup!  That worked.  I was hoping to avoid calc columns for each, since I'm pulling in Created, Started, Implemented and Target Date.  

Now I have CreatedToday, StartedToday, Denied Today, ImplementedToday, and then running totals.  Its a bit unelegant, but it is doing what I want.

[Greg_Deckler]

Cool. I tried it as a measure but kept getting the dreaded "cannot calculate unique value for [DateKey]" or whatever that error is. Someone that is better at DAX might be able to get around that by using EARLIER or something like that.

[ALeef]

Yeah, I got that too.  If it happens in CALCULATEs, just make sure you have the filter() around it - and it will resolve the [DateKey] just fine.

The reason it can't do it as a measure is that a measure is a singular value calculated using the filtered data.  Since we are looking for a data point at each date in the calendar, we have to use the column - a measure doesn't make sense.  Took me a while to wrap my head around it.

[rmiquel]

Hi,

This thread help me lots in getting a backlog in place!

So following this approach I managed to get a backlog over time. However I would like this backlog to react to filters from related tables to the "Projects" table (in your example, in mine I am looking at "support Cases")

So, although I am able to plot the historic backlog for open cases, I cannot filter by teams, which is maintained in a different table related to Support cases via UserId.

Not sure if what I am asking makes sense or is doable, but I've been hitting a brick wall trying to get this to work...any ideas will be very welcome!

[rmiquel]

Nevermind! Just as I posted this I found it...just needed to add a lookupvalue formula to the Support Cases table to bring the value from the related table, and then use it in the filter for the backlog colum in the data table.

Easy? 😛

[chaz2jerry]

Hi Miquel,

Can you elaborate on how you solved the issue with the slicer/filter from related tables?  I have same issue and I couldn't fully understand your solution.  I am a beginner in DAX so have not used Lookupvalue in the past.

"just needed to add a lookupvalue formula to the Support Cases table to bring the value from the related table, and then use it in the filter for the backlog colum in the data table."

EDIT: I am abloe to solve in following post, changing from Calc Column to Calc Measure approach and the slicers work now.

https://community.powerbi.com/t5/Desktop/Track-opportunity-pipeline-change-over-time-with-slicer-and...