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
Luodada
Helper I
Helper I

Caculating FactTable according to the valid date period in BOM table

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

1550085006893222018-05-012018-05-31
1660075006893242018-05-015999-12-31
1550086987859942018-06-015999-12-31
1660076987859982018-06-015999-12-31
16600716600712018-06-015999-12-31
155055006893232018-06-015999-12-31

 

BridgTable  to join with the factor Table :

  Product

50068932
69878599
166007

 

and A FactTable

ProductOrder dateOrder

500689322018-05-06100
500689322018-05-15200
500689322018-06-15100
500689322018-07-16400

 

Capture.JPG

 

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 

 

 

Capture.JPG

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

 

2 ACCEPTED SOLUTIONS
alena2k
Resolver IV
Resolver IV

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.

View solution in original post

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

 

 

View solution in original post

8 REPLIES 8
alena2k
Resolver IV
Resolver IV

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.

Capture.JPG

 

@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 

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

@alena2kthanks for Reply 

 

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

2.jpg

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

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

 

 

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

wow .  it works!!  

great !!!  

thanks @alena2k

 

 

Luodada
Helper I
Helper I

@Zubair_Muhammad

 

would you help me out , Zubair 

Zubair_Muhammad

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.