How to calculate Backlog Status

[nirajkrishna]

I have a fact table with the following columns:

JobID, Sales $s, Credit Date, Contract Date, Measure Date, Warehouse Date, Production Date, Installation Date. A job progresses through the production cycle in the same order as the way I have the columns listed. We want to be able to view our $'s that are in each cycle on any given date.

For example

[TABLE="width: 901"]
<tbody>[TR]
[TD]JobID[/TD]
[TD]Sales[/TD]
[TD]Credit Date[/TD]
[TD]Contract Date[/TD]
[TD]Measure Date[/TD]
[TD]Warehouse Date[/TD]
[TD]Production Date[/TD]
[TD]Installation Date[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]1/1/2013[/TD]
[TD="align: right"]1/7/2013[/TD]
[TD="align: right"]1/13/2013[/TD]
[TD="align: right"]1/19/2013[/TD]
[TD="align: right"]1/25/2013[/TD]
[TD="align: right"]1/31/2013[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]1/6/2013[/TD]
[TD="align: right"]1/12/2013[/TD]
[TD="align: right"]1/18/2013[/TD]
[TD="align: right"]1/24/2013[/TD]
[TD="align: right"]1/30/2013[/TD]
[TD="align: right"]2/5/2013[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]1/11/2013[/TD]
[TD="align: right"]1/17/2013[/TD]
[TD="align: right"]1/23/2013[/TD]
[TD="align: right"]1/29/2013[/TD]
[TD="align: right"]2/4/2013[/TD]
[TD="align: right"]2/10/2013[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1/16/2013[/TD]
[TD="align: right"]1/22/2013[/TD]
[TD="align: right"]1/28/2013[/TD]
[TD="align: right"]2/3/2013[/TD]
[TD="align: right"]2/9/2013[/TD]
[TD="align: right"]2/15/2013[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1/21/2013[/TD]
[TD="align: right"]1/27/2013[/TD]
[TD="align: right"]2/2/2013[/TD]
[TD="align: right"]2/8/2013[/TD]
[TD="align: right"]2/14/2013[/TD]
[TD="align: right"]2/20/2013[/TD]
[/TR]
</tbody>[/TABLE]

If I were to look at Contract $'s by date then I want to see:

[TABLE="width: 1165"]
<tbody>[TR]
[TD]Contract $'s[/TD]
[TD="colspan: 2"](Contract - Credit)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JobID[/TD]
[TD="align: right"]1/1/2013[/TD]
[TD="align: right"]1/2/2013[/TD]
[TD="align: right"]1/3/2013[/TD]
[TD="align: right"]1/4/2013[/TD]
[TD="align: right"]1/5/2013[/TD]
[TD="align: right"]1/6/2013[/TD]
[TD="align: right"]1/7/2013[/TD]
[TD="align: right"]1/8/2013[/TD]
[TD="align: right"]1/9/2013[/TD]
[TD="align: right"]1/10/2013[/TD]
[TD="align: right"]1/11/2013[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



And then for Measure $'s I want to see:

[TABLE="width: 1165"]
<tbody>[TR]
[TD]Measure $'s[/TD]
[TD="colspan: 2"](Measure - Contract)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JobID[/TD]
[TD="align: right"]1/1/2013[/TD]
[TD="align: right"]1/2/2013[/TD]
[TD="align: right"]1/3/2013[/TD]
[TD="align: right"]1/4/2013[/TD]
[TD="align: right"]1/5/2013[/TD]
[TD="align: right"]1/6/2013[/TD]
[TD="align: right"]1/7/2013[/TD]
[TD="align: right"]1/8/2013[/TD]
[TD="align: right"]1/9/2013[/TD]
[TD="align: right"]1/10/2013[/TD]
[TD="align: right"]1/11/2013[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I have a date table, but I do not know how to write the dax to make the numbers filter properly. Any help is appreciated.

 

[Jacob Barnett]

I've uploaded a potential solution here: https://docs.google.com/file/d/0Bz5yMU2oooW2TGZjMWdYWU1kOHc/edit?usp=sharing

That's the good news, the bad news is that I had to 'flatten' your fact table so that each entry has its own record - this is a far more common way for this kind of data to be used. I don't know how practical this is for you in real life but the likelihood is that the data was originally captured and stored in this format.

In a Nutshell I:

- Created a separate date table that contains unique, contiguous dates which is related to the fact table.
- Wrote measures that derive the 2 dates required for the first PIVOT (you will need to complete this task for all date types).
- Used the 2 date measures to write an excel IF style formula to determine whether that particular date was in the range in question. Crucially I included an ALL() against the date table that opened out the date context from the individual date in question (a CRUCIAL DAX technique).

And that's it. Hope this helps, happy to discuss further.
Jacob

 

[nirajkrishna]

Just what I was asking for! Perfect! I had my fact table flattened like you did, but I noticed performance degradation. Is that to be expected? I thought PP was great at handling columnar data. Maybe I had some bad measures. I cant really remember. Thanks for this!

 

[Jacob Barnett]

Glad it was useful. PowerPivot is ALWAYS better with flattened tables as the storage is columnar! There are a bunch of things you can try to improve performance but one of the weird ones is that sorting by low cardinality columns on import can significantly help performance in some circumstances.