March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi Community,
I could need your help.
I am working for a while on a formula and I cannot figure out how to get it to work.
I have to fact tables - sales and movements (granularity day, product, location). Both fact tables are linked with dimensions location, product and calendar. Based on sales and movements I am able to calculate the daily stock with a formula.
What I would like to achieve now is to create a formula that iterates over a calculated table by date, product and location and checks if there was stock available (stock > 0). Depending on that I'd like to set a binary 1/0 and then sum it up. So it would show me the number of days with stock on hand.
In order to keep to keep the calculations to a minimum my idea was to create a calculated table based on transactions (sales & movements) by product, location and transaction date and show the stock. based on the assumption that the stock cannot change between transcations I wanted to calculate the difference between the dates and multiply the number of days with 1 resp. 0.
I tried with summarize and addcolumns, union queries, etc. but nothing gave me what I was looking for.
I hope someone can help.
Let me know if I was clear or if you require more information.
Your help is greatly appreciated!
@PhMeDie , if you still facing the issue.Can you share sample data and sample output.
Hi @PhMeDie ,
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
I think you need to use calculatetable inside summarize . But unless we have sample data and sample output. We might able to tell.
https://docs.microsoft.com/en-us/dax/calculatetable-function-dax
Example is that if I need YTD, then I will move YTD to calculatetable inside summarize
@PhMeDie any chance you can provide a sample data reprentative of your problem that you mentioned with the desired end result with the logic to follow to achieve that goal. Would be great if you can!!!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
114 | |
76 | |
57 | |
52 | |
44 |
User | Count |
---|---|
164 | |
116 | |
63 | |
57 | |
50 |