Help to apply cash book logic to inventory level

[neil_floyd]

Hi!

I work at a company that lends products for sales representaives that eventually return the products.
So I am building a report which should display the current product quantity that each representative has in its hands.

Here is a  simplifield example of my fact table:

product_loan:

quantity	loan_date	return_date
5	01/jan/2023	15/jan/2023
10	05/jan/2023	20/jan/2023
3	20/jan/2023

I would like to be able to plot a chart like this, which display the current quantity not returned in each date (I have a Date Dimension Table):

What I want to achieve seems to be similar to a cash book logic, with debt, credit and balance for each day.

I am struggling with this because I have a Date dimension table, but I am not able to create a relationship with both loan_date and return_date at the same time. Also, I am not sure if this "current quantity not returned" should be a calculated  measure or if I should create calculated table instead to support this calculation.


I would appreciate any help.

[v-rongtiep-msft · Accepted solution]

Hi @neil_floyd ,

Please refer to my pbix file to see if it helps you.

Add a custom column.

List.Transform(try {Number.From([loan date])..Number.From([return date])} otherwise {Number.From([loan date])..Number.From(Date.EndOfMonth([loan date]))} ,each Date.From(_))

Then create a measure.

Measure = CALCULATE(SUM('Table'[quantity]),FILTER(ALL('Table'),'Table'[Custom]=SELECTEDVALUE('Table'[Custom])))

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[amitchandak]

@neil_floyd , refer

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

Use a date table