Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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 ...
DAX filter formula
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
JarroVGIT
Resident Rockstar
6 years ago
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's avatar
    Anonymous
    Not applicable
    6 years ago

     

    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