topic Re: Help with dax - product purchased during shelf life in DAX Commands and Tips

Help with dax - product purchased during shelf life

DJ1977 — Mon, 30 Aug 2021 17:56:17 GMT

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

Re: Help with dax - product purchased during shelf life

wdx223_Daniel — Tue, 31 Aug 2021 02:46:17 GMT

which date is the beginning of the shelf live?

Re: Help with dax - product purchased during shelf life

DJ1977 — Wed, 01 Sep 2021 07:46:37 GMT

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?

Re: Help with dax - product purchased during shelf life

DJ1977 — Wed, 01 Sep 2021 05:50:45 GMT

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

Re: Help with dax - product purchased during shelf life

Ashish_Mathur — Wed, 01 Sep 2021 11:47:00 GMT

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.

Re: Help with dax - product purchased during shelf life

DJ1977 — Wed, 01 Sep 2021 13:47:09 GMT

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?

Re: Help with dax - product purchased during shelf life

Ashish_Mathur — Wed, 01 Sep 2021 23:38:13 GMT

Hi,

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

Re: Help with dax - product purchased during shelf life

DJ1977 — Mon, 13 Sep 2021 10:07:59 GMT

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