March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to 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
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.....
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
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
20 | |
13 | |
10 | |
9 | |
7 |
User | Count |
---|---|
39 | |
27 | |
16 | |
15 | |
10 |