Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
---|---|
109 | |
102 | |
86 | |
77 | |
70 |
User | Count |
---|---|
120 | |
108 | |
98 | |
83 | |
77 |