Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to 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.
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
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.
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 .
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
117 | |
108 | |
108 | |
93 | |
70 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
94 |