Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am creating an AR report where I need to show payments against a customer account, customer invoice#, and the original amount
The issue Im having is the different payments go toward the original amount of 10,000
In the example above the customer made an initial payment of 5,000 lowering the balance to 5,000.
Then they made a payment of 1000 which shouldve lowered it to 4,000 but instead it went toward the intial balance of 10,000 making it 9,000
How do I create a measure that will take into account the new current balance? Any help would be appreciated
@AA622 , You need to have an inventory Approach, when using cumulative amounts. with help from date table
CALCULATE(SUM(Table[Amount Due]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Amount Paid]),filter(date,date[date] <=maxx(date,date[date])))
We sometimes use intial qty there. But I dobut that will apply here
example
Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))
Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))
refer
Power BI Inventory On Hand
Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw
Hey thanks I tried with the first measure but I still get the same results. This is what I made