using calculate function

[Anonymous]

Hi All,

Im new to power BI and i am having issue creating a DAX formula to achieve the result below

Table 1

ID	Description	Location	Remark	From Date	End Date
1234	Apple	London	Sold	01-01-20	01-01-21
1235	Mango	Paris	Sold	05-03-19	07-01-20
1235	Mango	Berlin	Available	08-01-20	09-12-21
1236	Pear	London	Sold	15-08-19	04-01-21
1237	Orange	Brussels	Sold	19-09-19	17-03-22

Table 2

ID	Description	Qty
1234	Apple	7
1235	Mango	9
1236	Pear	8
1237	Orange	6

Relationship: Table1 *:1 Table2

Question:

I would like to create a measure that shows the amount of fruits available within a particular period. 

ie 

Item	2019	2020	2021	2022
Apple	7	7(-1) =6	6+1	7
Mango	9(-1) = 8	8(+1) =9	9-1	8
Pear	8(-1) =7	7	7+1	8
orange	6(-1)= 5	5	5	6

Expected Result:

I intend to have the sum of all Item available on my Y axis and the Year on the X axis. 

Thank you as i anticipate your suggestions and responses

[JarroVGIT]

I really can't follow your logic. Why is Apple in 2020 7-1=6?  And ID in Table 1 is the same ID in table 2? What do the end dates say? And start dates? In other words: please explain your tables a bit, this seems like a very rare datamodel to be honest.

Thanks!

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Anonymous]

Hi @JarroVGIT 

Thank you for your response.

I can imagine that the logic is a bit complex. However, to answer your questions

Q1: Why is Apple in 2020 7-1=6?

Apple in 2020 is 7 -1 = 6 because from the inventory (table 2), the total amount of apples are 7 and in 2020 and apple is rented out from 01-01-20 till 01-01-21 (table 1, row 1). The means that the apple with ID 1234 would be reduced by 1 from 01-01-20 i.e "from date" , till the01-01-21 i.e "end date" when it becomes available again. 

Remark: Sold = Rented (when item is rented, 1 is subtracted(-1) from the total qty in the inventory(table 2)

                          Available (when item is rented, 1 is added (+1) from the total qty in the inventory(table 2)

Q2: ID in Table 1 is the same ID in table 2?

Yes, the ID's are the same. The ID are unique identifiers for the items. the relationship is a many to one relationship.

The whole idea is to create a measure to know the qty of items in the inventory that are available to be rented out everytime(in this case yearly). 

Lets me know if it is clear now @JarroVGIT 

Best Regards,

Charlie101

[JarroVGIT]

I think I see your point what you are trying to achieve but the logic is very incomplete. What if an item is returned mid year? Then it is counted as inventory in that year, but if it is returned on 31-12, it isn't? Where is that border, what if it is rented till 30-12? Why is there a line for available for Mango but not for the others?
My tip for you is: reconsider your datamodel and logic you want to apply, as this will become messy quickly.

[Anonymous]

Thanks for the tip @JarroVGIT !

The from date and the end date in table 1 would be linked to a date table. With the date table, the date can be shown as Day, Months or Year. 

Available implies that the item has been returned and is available to be rented out for the period between the start date till the end date