Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I'm trying to create a measure on projected ending balance of inventory as: beginning balance + est. purchase (based on % of beginning: #s dependent of beginning balance) - est. shipment (#s independent of this calculation) = ending balance.
Below is what i'd like to achieve:
Month | Current | +1 | +2 | +3 | +4 |
beg | 1,000 | 700 | 520 | 412 | |
purchase | 100 | 100 | 100 | 100 | |
out | 400 | 280 | 208 | 165 | |
end | 1000 | 700 | 520 | 412 | 347 |
Basic data:
Date | on-hand inv ($) |
8/31/2020 | 1000 |
purchase data:
Date | purchase |
8/31/2020 | 100 |
9/30/2020 | 100 |
10/31/2020 | 100 |
11/30/2020 | 100 |
out data: 40% of beginning balance (recursive)
@yaizawa , can you share sample source data for this
@yaizawa - What does the source data look like? Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, 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
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
Thanks for the tip as this was my first time posting a question.
added simple sample data. it looks like DAX won't be able to perform recursive measure to take % of beginning balance..
@yaizawa - Oooo, yeah DAX hates recursive with a passion, I have fought with it for ages. This is the best I have come up with:
https://community.powerbi.com/t5/Community-Blog/Previous-Value-Recursion-in-DAX/ba-p/638320