Caculating FactTable according to the valid date period in BOM table

[Luodada]

HI 

I have posted this question yesterday , and gotten an answer which partially solved the issue , but I have already marked it as a solution .  so I have reposted it here , and ask for help , I hope it is ok .

I have 3 table,  BOM table as below

Item	Product	pcs	From date	To date
155008	50068932	2	2018-05-01	2018-05-31
166007	50068932	4	2018-05-01	5999-12-31
155008	69878599	4	2018-06-01	5999-12-31
166007	69878599	8	2018-06-01	5999-12-31
166007	166007	1	2018-06-01	5999-12-31
15505	50068932	3	2018-06-01	5999-12-31

a Bridgetable

Product

50068932

69878599

166007

and a Fact Table

Product	Order date	Order
50068932	2018-05-06	100
50068932	2018-05-15	200
50068932	2018-06-15	100
50068932	2018-07-16	400

I would like to cacuate the [Order] in the FactTable according to the valid date frame in BOM Table

for example >

for  item155008 , product 50068932 ,  I should get order statistics In May 2018 , but NOT in June and July ,as the [to date] in the

BOM Version is expired from 20180531

then I have gotten an answer , which is right on the lower level - Product sum level ,  but no figure on aggregated level as below picture ,   anyone knows how to fix it ？

Order Filtered = CALCULATE(SUM(FactTable[Order]), 
                           FILTER(FactTable, 
                                AND(FactTable[Order date] >=  SELECTEDVALUE(BOM[From date]), 
                                    FactTable[Order date] <= SELECTEDVALUE(BOM[To date]))
                                  )
                           )

here is the example in power BI 

https://drive.google.com/drive/folders/1P8AvsM0E5Ce4N6Aw9sDsFBtWXkl_0wa9

https://drive.google.com/file/d/1zhVRb87pmUbvti2k8rGmYvpA561z3bhK/view?usp=sharing

thanks in advance

Luodada

[#2 · Accepted solution]

Order Filtered = CALCULATE(SUM(FactTable[Order]), 
                           FILTER(FactTable, 
                                AND(FactTable[Order date] >=  SELECTEDVALUE(BOM[From date]), 
                                    FactTable[Order date] <= SELECTEDVALUE(BOM[To date]))
                                  )
                           )

[v-lili6-msft · Accepted solution]

hi,@Luodada

    You can add this measure as below:

Measure = CALCULATE(SUMX(FactTable,[Order according to timeframe]))

Result:

Best Regards,

Lin

[Luodada]

@v-lili6-msft

that is great !

thanks Lin !