how to build fact cash balance

[RJP]

Hi, 

can anyone sugget on the challenge i am facing on to build a fatc cash balance table for cash billing  reporting and  then the desire measures and caculated column i needed to build.

We have a source system data for the product we sell for different providers and then billing them to get the commision and return the commisoin when product is returned 

We have Product Sales table in source to keep the sales records for each products solds and there are associsted expected commmision and expected return amount set for every product we sold.

And then we have Transaction table for to keep the records for every providers how mauch commissionthey paid to us and hoe much we returned to them when product is returned  after some days.

see below tables 

Now what we want to achieve is we need to build the fact table from this 2 sources where we struggle the most  to achieve the below requiremnts please guide or help us how should we structure the this fact table please can anyone provide the guide on this please.. 

and then to  calcualate the profit and loss to get the every month 

how much cash we received and how much we retrund against the expected Received amount and expected return amount.

And then to see in details for every product level how much balance left for Cash received Balance and Cash Returned balance and then Net balance.

Also to check the Cash status like this 

We want to analyse the detail for each product level of their balances and their cash status.

[RJP]

Hi @v-heq-msft ,
thank you so much for yout time and effort..

just last question i mentioned is then How to create the cash statuses like paid, unpaid, dwe etc.... 
Is is using calcualted column OR Measure.....

Beacasue then we need to Report the count of this status like How many Paid, How many unpaid etc..... 

[v-heq-msft · Accepted solution]

Hi @RJP ,
If you want to get the final state, it's easy. You just need to create a MEASURE and use if statements to determine the output based on different criteria.

Cash Received Status = 
IF(
    [Cash Received Amount] > 0 ,
    "Overpaid",
    IF(
        [Cash Received Amount] < 0 ,
        "pending",
        IF(
            [Cash Received Amount] = 0 && [Received Amount] <> BLANK(),
            "paid"
        )
    )
)

Cash Returned Status = 
IF(
    [Cash Returned Amount] < 0 ,
    "owed",
    IF(
        [Cash Received Amount] = 0 && [Return Amount] <> BLANK() || [Expected_return_amount] <> BLANK(),
        "paid"
        )
    )

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[v-heq-msft]

Hi @RJP ,
First unpivote the Product Sales and Renamed the column

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PYvRDcAgCER34dtAORB1FuP+a1RMa0IC9443JymMCukjCMED7NAaOLd9LB98GFulVeauWxpif1dj8EjmAr+wKiOO0Hs/pV4D0dljHzlrvQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProductKey = _t, #"Sale Date" = _t, #"Expected Received Amount" = _t, #"Cancelled Date" = _t, #"Expected Return Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProductKey", Int64.Type}, {"Sale Date", type date}, {"Expected Received Amount", type number}, {"Cancelled Date", type date}, {"Expected Return Amount", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ProductKey", "Expected Received Amount", "Expected Return Amount"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "DateType"}, {"Value", "Statement Date"}})
in
    #"Renamed Columns"

Then Combine two tables and remove columns

let
    Source = Table.Combine({#"Product Sales", Transaction}),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Expected Received Amount", "Expected Return Amount", "DateType", "Amount", "CashType"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Statement Date"})
in
    #"Removed Duplicates"

Create relationship by date

Create Measures

Received Amount = 
CALCULATE(
    MAX('Transaction'[Amount]),
    FILTER(
        'Transaction',
        SELECTEDVALUE('Transaction'[CashType]) = "Received"
    )
)

Return Amount = 
CALCULATE(
    MAX('Transaction'[Amount]),
    FILTER(
        'Transaction',
        SELECTEDVALUE('Transaction'[CashType]) = "Return"
    )
)

Expected_received_ amount = 
CALCULATE(
    MAX('Product Sales'[Expected Received Amount]),
    FILTER(
        'Product Sales',
        SELECTEDVALUE('Product Sales'[DateType]) = "Sale Date"
    )
)

Expected_return_amount = 
CALCULATE(
    MAX('Product Sales'[Expected Return Amount]),
    FILTER(
        'Product Sales',
        SELECTEDVALUE('Product Sales'[DateType]) = "Cancelled Date"
    )
)

Cash Returned Amount = [Return Amount] - [Expected_return_amount]

Cash Received Amount = [Received Amount] - [Expected_received_ amount]

Final output

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly