Forum Discussion
Aggregate $Amt from fact table based on dynamic date range from a different fact table
I've been struggling with this one for a while. I know the solution is going to be fairly easy and I'll be kicking myself for having spent such a long time blanking on this. Here is what I'm trying to accomplish:
Calculate the total 'recon' (reconditioning) spend by vehicle. For each Stock #, I need the total RO $Amt from type 'Internal' only from FactServHist but only include RO's that fall between the Recon Start and Recon End dates found in FactInventory (inclusive of start/end dates).
I have 5 tables involved in this example:
- Date (includes Date, DateSK in text format, day, year, month, qtr, etc)
- DimInventory (houses stock#, dealer that owns it, and all info regarding the vehicle. Stock # is unique for this table)
- FactInventory (houses workflow-driven data including process start and complete dates)
- FactServHistory (houses every Repair Order - RO# $Amt, date complete, Stock#, etc)
- RO Ref (reference table created from FactServHist - Stock# lookup by RO#- this example doesn't show it but in the source data the RO detail is stored by line item and so there are several rows per RO)
Is FactInventory to DimInventory 1:1 or Many:1 ?
If 1:1, you can create a relationship between FactServHist and FactInventory, then your measure should be something like
TotalRecon = sumx( FILTER(FactServHistory, FactServHistory[Type]="INTERNAL" && FactServHistory[Date] >= RELATED(FactInventory[Recon Start]) && FactServHistory[Date] <= RELATED(FactInventory[Recon Finish])), FactServHistory[$AMT] )If DimInventory : FactInventory is not 1:1, (and therefore FactServHistory : FactInventory = many : many) you'll probably need additional bridge tables to be able to find the right dates in the FactInventory table.
Hope this helps
David
5 Replies
- dedelman_clng
Community Champion
Is FactInventory to DimInventory 1:1 or Many:1 ?
If 1:1, you can create a relationship between FactServHist and FactInventory, then your measure should be something like
TotalRecon = sumx( FILTER(FactServHistory, FactServHistory[Type]="INTERNAL" && FactServHistory[Date] >= RELATED(FactInventory[Recon Start]) && FactServHistory[Date] <= RELATED(FactInventory[Recon Finish])), FactServHistory[$AMT] )If DimInventory : FactInventory is not 1:1, (and therefore FactServHistory : FactInventory = many : many) you'll probably need additional bridge tables to be able to find the right dates in the FactInventory table.
Hope this helps
David
Getting close! Looks like I need to clean up the source data a bit and tinker with the date ranges (it's not including RO's completed on the start/end dates but the ones in between are pulling in at least) but I'm getting some values at least. Thank you!
- dedelman_clng
If the date on the FactServiceHistory table has a time component, but the dates on FactInventory does not, that may be where are seeing the discprepancies.
for example, 1/15/2017 14:35:00 > 1/15/2017 - even though both may look like just dates there could be hidden times.
I've been struggling with this one for a while. I know the solution is going to be fairly easy and I'll be kicking myself for having spent such a long time blanking on this. Here is what I'm trying to accomplish:
Calculate the total 'recon' (reconditioning) spend by vehicle. For each Stock #, I need the total RO $Amt from type 'Internal' only from FactServHist but only include RO's that fall between the Recon Start and Recon End dates found in FactInventory (inclusive of start/end dates).
I have 5 tables involved in this example:
- Date (includes Date, DateSK in text format, day, year, month, qtr, etc)
- DimInventory (houses stock#, dealer that owns it, and all info regarding the vehicle. Stock # is unique for this table)
- FactInventory (houses workflow-driven data including process start and complete dates)
- FactServHistory (houses every Repair Order - RO# $Amt, date complete, Stock#, etc)
- RO Ref (reference table created from FactServHist - Stock# lookup by RO#- this example doesn't show it but the source data is more complex and RO# in FactServHist is not unique so I had to create and index to use as PK/FK
