Revenue collection cash received against expected amount and cash paid out against expected return

[RJP]

We need to analyse the client payment statement cash data.

we have a data as shown here in image

every Product sold there is a Expected amount and sold date associated with and when Product return there is a Expected Return amount and Return date associated with.   So when we received the payment we need to check the received amount match against the expected amount and when product return need to check the Return amount match against the Expected Return amount.
Then need to check the remaining balance and need to to create a status column like paid/Unpaid/Overpaid/Underpaid.   

So statement date is join with the Date Dimension to analyses each Month/ by date How much Payment we receiving and how much payment we are returning.

AND Also need to analyse Profit & Loss where need to use Expected amount (Which use sold date column ) against the Actual amount ( Which used Statmewnt date ) to see how much balance over paid or under paid or not paid .

here is some question to achieve this 
How can we create balance for the where doing the  ( total expected Amount - expected return amount )  and less the actual Amount(which is a Received amount - Return amount)

create dynamic status for any (Paid, Unpaid, Over paid, under paid ) to track the details.

Is there a dynamic way to create the balance and net balance and status.

[ManuelBolz]

Hello @RJP ,

If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

Did I understand you correctly? I hope this helps you.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZJRjsMgDETvkm8L2WNj4CxV73+NhYQkQHeltivlw87A89j48digjo02eBAOYLREJQhaojVJCQSugSWh0gJoYHmRmZi3J31H9MDaZDtBVSxKGk9iuwcOctWtX7FMlu4bMhaZxUYR4ZLbj9O+rUn0cvi5glf530hYYFt6jVEI3pG625bcj7VmdOwcWalgChA4TrLMLt9CyoisZ+9ZdpdwJsSONMWKnJIshaL14Hg++00eXL6DdP0T2V2mgiaeSOvzuUa+L4r6UH15E3jdHF+tfc7JlbMv/8F5/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProductKey = _t, SoldDate = _t, ReturnDate = _t, ExpectedAmount = _t, ExpectedReturnAmount = _t, StatementDate = _t, ReceivedAmount = _t, ReturnAmount = _t]),
    Type = Table.TransformColumnTypes(Source,{{"ExpectedAmount", Int64.Type}, {"ExpectedReturnAmount", Int64.Type}, {"ReturnAmount", Int64.Type}, {"ReturnDate", type date}, {"StatementDate", type date}, {"ReceivedAmount", Currency.Type}}),

    ColumnExpectedNetAmount = Table.AddColumn(Type, "ExpectedNetAmount", each [ExpectedAmount] - [ExpectedReturnAmount], Currency.Type),
    ColumnActualNetAmount = Table.AddColumn(ColumnExpectedNetAmount, "ActualNetAmount", each [ReceivedAmount] - [ReturnAmount], Currency.Type),
    ColumnBalance = Table.AddColumn(ColumnActualNetAmount, "Balance", each [ExpectedNetAmount] - [ActualNetAmount], Currency.Type),
    ColumnStatus = Table.AddColumn(ColumnBalance, "Status", each 
        if [Balance] = 0 then "Paid" 
        else if [Balance] > 0 then "Unpaid" 
        else if [Balance] < 0 then "Overpaid" 
        else "Underpaid", type text),
    ColumnProfitLoss = Table.AddColumn(ColumnStatus, "Profit & Loss", each [ExpectedAmount] - [ActualNetAmount], Currency.Type)
in
    ColumnProfitLoss

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github

[RJP]

Thanks for your reply..
Can we achieve this using the calculated column Or measure....

[ManuelBolz · Accepted solution]

Hallo @RJP ,

i don't know exactly what you mean, but that would be the DAX code for the custom columns. Here you have to replace “YourTable” with the correct table name in your semantic model.

If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

ExpectedNetAmount = 
SUM('YourTable'[ExpectedAmount]) - SUM('YourTable'[ExpectedReturnAmount])

---

ActualNetAmount = 
SUM('YourTable'[ReceivedAmount]) - SUM('YourTable'[ReturnAmount])

---

Balance = 
[ExpectedNetAmount] - [ActualNetAmount]

---

Status = 
SWITCH(
    TRUE(),
    [Balance] = 0, "Paid",
    [Balance] > 0, "Unpaid",
    [Balance] < 0, "Overpaid",
    "Underpaid"
)

---

ProfitLoss = 
SUM('YourTable'[ExpectedAmount]) - [ActualNetAmount]

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github.