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

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'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