The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
I have problem's to connected my total suply measure with my date table.
I need to calculate the total supply of items filter by subgroup's
The change of the total supply must be in the month when the articel is purchased or saled.
See the Pbix file, thanks.
Beste Regards,
Ronald
https://drive.google.com/file/d/0B1cNUVQxpE63cE9Wa056TXRLVzg/view?usp=sharing
Solved! Go to Solution.
Hi @Ronald123,
You can mark it as answer first, becasue it gets the right result. I will research it when I free. Thanks for understanding.
Best Regards,
Angelia
Hi @Ronald123,
Please use the formula below.
Total suply2 = CALCULATE ( SUM ( 'Stock'[Amount] ), FILTER (ALL('Date'), 'Date'[Date]<=MAX('Date'[Date])) ) +CALCULATE ( SUM ( 'Stock'[Sale] ), FILTER (ALL('Date'), 'Date'[Date]<=MAX('Date'[Date])) )
I test it in the .pbix file, there is blank row, so there is this a blank month row as the screenshot.
I have no your resouce table excel file, so can't edit the Power Query. Please right click->Edit Query->Remove blank rows as shown in highlighted in yellow background, and check if it works fine.
Best Regards,
Angelia
@v-huizhn-msft, Angelia, Thank you for you're reaction.
The blank row isseu is solved, de datetable was loaded until 30-12-2017, the new pbix file i have attached in this message.
The isseu is there still, see at mont number 5. Until May there must be a total suplly of 4. Item 24, of subgroup fruit has a saled date of 2 June 2017. The measure gives the total of 3, and that is wrong. Can you take a look on the "This is what i need table" in de the pbix file?
Thanks,
Ronald
https://drive.google.com/open?id=0B1cNUVQxpE63ZXkxdXZJbnRWZkU
Hi @Ronald123,
After research, I finnaly get the expectedresult, but I still need to find out how to calculate, I post it here for your reference, then we can discuss.
Create measure using the formula.
Measure = VAR currentDate = MAX ( 'Date'[Date] ) RETURN CALCULATE ( SUM ( 'Stock'[Amount] ), FILTER ( Stock, ( Stock[Date purchase] <= currentDate && Stock[sale-date] >= currentDate ) ) )
Then create another measure using the formula below. I wonder how to get right result without sale value.
Measure 2 = CALCULATE(Stock[Measure],FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date])))
I still research it and post the update, thanks for understanding.
Best Regards,
Angelia
Hello Angelia,
The measure works fine. Are you still resarch the measure or can i accept this solution?
Beste Regards,
Ronald
Hi @Ronald123,
You can mark it as answer first, becasue it gets the right result. I will research it when I free. Thanks for understanding.
Best Regards,
Angelia
@v-huizhn-msft Hello Angelia,
Thank you for researching my question.
I have the follow problem when i created the measure.
[sale-date] is not a colum in de pbix file, when i try the colum [date sale], this give me the wrong totals.
Is it possible to share the pbix file?
Beste Regards,
Ronald
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
108 | |
76 | |
63 |
User | Count |
---|---|
270 | |
129 | |
123 | |
100 | |
92 |