Complex ask - help with strategy/technique/formula

Anything related to PowerPivot and DAX Formuale
golfboy7
Posts: 3
Joined: Sun Nov 29, 2020 11:22 pm

Complex ask - help with strategy/technique/formula

Postby golfboy7 » Mon Nov 30, 2020 1:31 am

Good morning all, and if you're in the US, Happy Thanksgiving!

I've been working on a Capacity model for my employer and have hit a road block. Looking for wisdom on this problem and appreciate your thoughts.

Data granularity is at the project level where a Project has a Created date, a Live date, and a Closed date.

1. Incoming Volume (easy to measure by month of Created Date)
2. Carrying Volume (this is my road block)
3. Live Volume (easy to measure by month of Live date)
4. Closed Volume (easy to measure by month of Closed date)
5. Total work is the sum of 1 through 4 above for any given month

This model is in excel connected directly to our data lake, and as such is not "storing results" for each month; that is to say I don't want to manually manipulate this each month by adding a column...etc. I want this connection so we can refresh it quickly at each month end without having to paste results into a sheet. Using PQuery to shape data, ultimately delivering it to PPivot data model for further analysis.

Elaborating on Item #2 above, my road block. Any project can span a single month, or multiple months based on complexity and client readiness. The calculation I've settled on is:

"Any project with a month of Created Date prior to the month in question, and where the month of Live Date is either blank (because it's still being worked), or is later than month in question."

Example Project:
Created 6/15/2020
Live 9/15/2020
Closed 10/15/2020

In June, this would show as Incoming Volume.
In July and August, this would show as Carrying Volume.
In September, this would show as Live Volume.
And in October, show as Closed Volume.

As you can see from this definition, there is not a single field to reference for any given month.

How would you approach this problem?

My thoughts so far have not been fruitful.
1. I've tried using a calendar table and performing the calculation there in a calculated column. This appears to work at the aggregate level, but I will need to perform further analysis by the group owning the projects which is on my main table. There is no clear relationship between the calendar table and the 'Carrying timeframe', so I would have to create a calculated column in the Calendar table for each group. Right now this would mean 6 columns, but it could easily expand to more if the leadership wants a different cut, so I'm hoping for a more elegant solution.

2. My new idea is to create a measure for each of the 24 rolling months for this calculation. I haven't executed on this yet since we're on holiday but it may work if done appropriately.

3. Perhaps I need to shape the data differently using PQuery. I'm certainly open to this.

What are your thoughts at this point? Is this something you've encountered before and how did you approach the problem?

MattAllington
Posts: 1150
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Complex ask - help with strategy/technique/formula

Postby MattAllington » Mon Nov 30, 2020 6:00 am

To me, this is the same problem as I wrote about here https://exceleratorbi.com.au/calculatin ... ax-part-2/. My article is about time, but it’s the same with dates. I would use this technique to calculate the total, then you can subtract the others to get the missing number
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

golfboy7
Posts: 3
Joined: Sun Nov 29, 2020 11:22 pm

Re: Complex ask - help with strategy/technique/formula

Postby golfboy7 » Mon Nov 30, 2020 6:42 am

Matt, this is great, thank you for sharing. I may be able to use your blog post to find a solution.

Follow up question about your propsed solution; since the calendar table has no rwlationship with the data table, what method would you have used (in your solution), if the user wanted to analyze the ticker times by a field in the ticket data?

Say, for example, different types of tickets, or different people handling them...etc.

Would you employ a separate calculation for each in the calendar table? Or would this request warrant exploring a different solution?

MattAllington
Posts: 1150
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Complex ask - help with strategy/technique/formula

Postby MattAllington » Mon Nov 30, 2020 5:58 pm

Mmmmm, I’m having second thoughts now. Does each project have the 3 date columns in your data against a single row in the table (ie the row for each project)?
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

golfboy7
Posts: 3
Joined: Sun Nov 29, 2020 11:22 pm

Re: Complex ask - help with strategy/technique/formula

Postby golfboy7 » Wed Dec 02, 2020 3:06 am

It does right now as I have shaped the data in this way by pivoting the Dates table and joining it to the project table. I can reshape this data if another approach is warranted.


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests

cron