Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be 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

Reply
PhMeDie
Helper I
Helper I

Rate of sale calculation

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!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4 REPLIES 4
amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
v-yingjl
Community Support
Community Support

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
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
smpa01
Super User
Super User

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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.