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

     

dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
9 years ago

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.

ImaDAXman's avatar
ImaDAXman
Frequent Visitor
9 years ago

Thanks for the additional suggestion.  That was the first thing I checked.  Great minds think alike :)

 

I ended up figuring out the issue - the source data coming from the system randomly was inserting a space in front of some of the RO #'s so once I went in and used the transform > trim feature everything is pulling over now.  Thanks again for all your help!