Rate of sale calculation

[PhMeDie]

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!

[amitchandak]

@PhMeDie , if you still facing the issue.Can you share sample data and sample output.

[v-yingjl]

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.

[amitchandak]

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

[smpa01]

@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!!!