Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ImaDAXman
Frequent Visitor

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)

 

            DimInventory.PNG  FactInventory.PNG        

 

 

 FactServHist.PNG

 

 

 

            Expected Output.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
dedelman_clng
Community Champion
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

 

View solution in original post

5 REPLIES 5
dedelman_clng
Community Champion
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!

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.

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!

ImaDAXman
Frequent Visitor

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

 

            DimInventory.PNG  FactInventory.PNG        

 

 

 FactServHist.PNG

 

 

 

            Expected Output.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.