Caculating FactTable according to the valid date period in BOM table

[Luodada]

HI Guys 

I need some help here , I am stucking here for a while , hope someone could help me out , really appreciate

basically I have 3 table 

BOM Table  with   "in and out date" on the Item  for the Product

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

BridgTable  to join with the factor Table :

  Product

50068932

69878599

166007

and A FactTable

ProductOrder dateOrder

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 is expired from 20180531

from 20180601 is another item 15505 replaced it , and stay inside the product

what Dax I should use ?

many thanks 

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

[alena2k · Accepted solution]

hi! Try this measure and see if it is what you need:

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

If not, please share original requirement in business terminology.

[alena2k · Accepted solution]

hi! Try this measure and see if it is what you need:

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

If not, please share original requirement in business terminology.

[Luodada]

@alena2k 

the result is correct on the lower product level , but  I could not get the sum  on the aggregated level .  do you know how to fix it 

[alena2k]

Define "aggregate level", please. Are you aggregating by month or running total or something else? 

[Luodada]

@alena2kthanks for Reply 

both on monthly level and item level 155008 , as you could see below picture , it is blank on subtotal .

here is the example in POWER BI 

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

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

[alena2k · Accepted solution]

We need to use SUMX to get totals.  I've change measures the way I prefer: first I find multiplier for the time period/item in BOM assuming that there is only one and then I calculate total item pieces. I am sure that there are more ways to get there, so consider it as an idea

https://www.dropbox.com/s/3sqazi078ugpkw0/example%20multiplier.pbix?dl=0

[Luodada]

thanks @alena2k

it works perfect `! even though I dont quite understand the logic of making the differences on result , between using sumx directly in same measure , and do it seperately by creating a mutiplier first .   

thanks a lot

Luodada

[Luodada]

wow .  it works!!  

great !!!  

thanks @alena2k

[Luodada]

@Zubair_Muhammad

would you help me out , Zubair 

Zubair_Muhammad