Forum Discussion

ImaDAXman's avatar
ImaDAXman
Frequent Visitor
9 years ago
Solved

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...
Need Help
  • dedelman_clng's avatar
    dedelman_clng
    9 years ago

    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

     

ImaDAXman's avatar
ImaDAXman
Frequent Visitor
9 years ago

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