Help with dax - product purchased during shelf life

[DJ1977]

Hello. 

I would have greatly appreciated if someone could help me with a dax formula for the following case:
I have a dimension table for product and a fact table for sales. Can also add that I have a standard calendar table, as it may be relevant here.
The products are food and have a shelf life. The different products have different shelf lives.

I would like to have a dax that calculates whether the different products are bought by the different customers during the products' shelf life. No need to consider how much, just whether it is purchased or not.

My next step is to calculate the number of customers bought divided by the number of customers, so that I have an index of the distribution of the various products. Below I have set up an example of the relevant tables:

Best wishes

DJ

[wdx223_Daniel]

which date is the beginning of the shelf live?

[DJ1977]

Hi and thanks for the reply.
Shelf life is the number of days the product lasts. That is no fixed date. 

I want to find out if the customer has bought the product, based on today's date, and x number of days back. X here becomes shelf life for the product. So for productID 5176 i want to calculate if customer have bought the product within the last 22 days. (ref DimProduct table)

I have now found an expression that almost takes me where I want, but not quite, it is this:
----
Quantity Purchased Shelf life =
VAR Last_date = LASTDATE (FactSales [Date])

Return
CALCULATE (DISTINCTCOUNT (FactSales [CustomerID]),
FILTER (ALL (DimCalendar),
DimCalendar [Date]> Last_date - 20 &&
DimCalendar [Date] <= Last_date))
----
In this expression, "Last_date - 20" means that the shelf life is always 20 days. How can I replace 20 with an expression that uses the Product shelf life from the DimProduct table?

@wdx223_Daniel Do you see any solutions?

[DJ1977]

@Ashish_Mathur Do you have any suggestion to how i can adjust my formula for my purpose?

 

[Ashish_Mathur]

Hi,

Shouldn't you also have the date of manufacturing of each product?  Also, please clearly show the result you are expecting with an explanation.

[DJ1977]

Hi, and thanks for answering @Ashish_Mathur .
The case is perhaps a bit unclear, and would like to explain in more detail what I am looking for.
We have regular customers who are committed to maintaining a range of fresh products. These products have different shelf lives.
I want to know which customers have bought the products at least once during this shelf life. This is measured on the basis of today's date, and x days back, where x = shelf life.

Results should be displayed as a table, which shows the product, number of customers purchased during the shelf life, and an index. For example:

Index = number of customers purchased during the shelf life / total number of customers. It's the counter in this fraction I'm struggling with. As previously explained, I have come close with the dax expression below:

Number of customers purchased shelf life =
VAR Last_date = LASTDATE (FactSales [Date])

Return
CALCULATE (DISTINCTCOUNT (FactSales [CustomerID]),
FILTER (ALL (DimCalendar),
DimCalendar [Date]> Last_date - 20 &&
DimCalendar [Date] <= Last_date))

In this expression, "Last_date - 20" means that the shelf life is always 20 days. I am therefore wondering how I can make this dax expression so that it is dynamic in relation to the column Product shelf life from DimProduct table?

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[DJ1977 · Accepted solution]

Hello, i found a DAX that sorted this out for me. This is the solution to my question:

Quantity Purchased Shelf life =
VAR Last_date = TODAY()
VAR Shelf = SELECTEDVALUE(DimProduct[ProductShelflife])

Return
CALCULATE( DISTINCTCOUNT(FactSales[CustomerID]),
FILTER( ALL(DimCalendar),
DimCalendar[Date] > Last_date - Shelf &&
DimCalendar[Date] < Last_date))