Forum Discussion
Diving two columns from different tables that cannot be summed up
Hi all,
I have a master table for products with their expiry date(in days). For eg 180 days
I have a stocks table for products with their manufacture date.(same products will have different manufacture date)
Now I want to calculate the percentage of expiry( no of days that have passed from maufacture date/no of days that the product is good to use) of each product and manufacture date combination as on today.
Since the columns to be divided are in different table, how do I divide them without summing them up.( Cannot create a measure beacuse that will sum up the values, which is wrong)
Please help
I have a master table for products with their expiry date(in days). For eg 180 days
I have a stocks table for products with their manufacture date.(same products will have different manufacture date)
Now I want to calculate the percentage of expiry( no of days that have passed from maufacture date/no of days that the product is good to use) of each product and manufacture date combination as on today.
Since the columns to be divided are in different table, how do I divide them without summing them up.( Cannot create a measure beacuse that will sum up the values, which is wrong)
Please help
You can totally create a measure, just don't create one that sums up the values.
I'm assuming you have some sort of productID that relates from the master table to the stocks table.
Without knowing your table structure, your measure would look something like this:
Expiration% = DATEDIFF(TODAY(), SELECTEDVALUE(Stocks[ManufactureDate]), DAY)/ SELECTEDVALUE(Master[ExpiryLength])
Put this in a table with your list of items from the Stocks table, and it should give you correct values.
2 Replies
- Cmcmahan
Resident Rockstar
You can totally create a measure, just don't create one that sums up the values.
I'm assuming you have some sort of productID that relates from the master table to the stocks table.
Without knowing your table structure, your measure would look something like this:
Expiration% = DATEDIFF(TODAY(), SELECTEDVALUE(Stocks[ManufactureDate]), DAY)/ SELECTEDVALUE(Master[ExpiryLength])
Put this in a table with your list of items from the Stocks table, and it should give you correct values.