Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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! 🙂
Proud to be a Super User!
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
Proud to be a Super User!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
40 | |
27 | |
20 | |
14 | |
8 |
User | Count |
---|---|
76 | |
48 | |
46 | |
20 | |
16 |