Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I am trying to figure out how to calculate how many weeks of supply of product I have based on my sales and the 'ending on hand' I have currently.
The problem comes with pivots always combining the data in the 'ending on hand'
I have data from several weeks being combined in the Data Model.
Example
past 3 weeks
Week 1
Item | Sales | EOH
A | 3 | 7
B | 2 | 4
Week 2
A | 3 | 4 (Week 1 EOH - Week 2 Sales)
B | 2 | 2
Formula: EOH (most current week) / (Sum of Sales/Count of weeks with sales (disctinct count))
Solved! Go to Solution.
I was able to do it using CALCULATE
EOP =
CALCULATE (
SUM ( [EOP Inv Qty] ),
FILTER ( Weekly, [Fiscal Week Number] = MAXX ( Weekly, [Fiscal Week Number] ) )
)
Hi hrojas3,
I am not clear about your requirement, what did you mean "The problem comes with pivots always combining the data in the 'ending on hand'"? If possible, could you please explain it in details? In addition, if possible, could you please inform me your sample data and expecting output? Then I will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
I was able to do it using CALCULATE
EOP =
CALCULATE (
SUM ( [EOP Inv Qty] ),
FILTER ( Weekly, [Fiscal Week Number] = MAXX ( Weekly, [Fiscal Week Number] ) )
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |