Forum Discussion
using calculate function
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
4 Replies
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! 🙂
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
- 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.
