Lost Product

[H_insight]

Hi All,

I am trying to identify lost product within my sample file for over 3 months ago. So All products thats meet the below criteria and over than 3 months old (rolling).

Criteria as follows: 1. product (col)= Production 2. Action (col) = View 3. Period is rolling last 3 months

Here is my DAX attempt:

Lost Product = 
VAR _Period = DATESINPERIOD(Data[Date],MAX(Data[Date]),-3,MONTH)
Return
CALCULATE(DISTINCTCOUNT(Data[Product]),NOT(Data[Date]) in _Period,FILTER(Data, Data[Product] ="Production"),FILTER(Data,Data[Action] = "View"))

The above dax return 1 record only, where I was expecting 4 records.

Any advice or tips would be much appreciated.

Sample file Link.

Thanks, H

[v-yanjiang-msft]

Hi @H_insight ,

According to your description, here’s my solution. “Lost Product” is a column. as in the visual, the same options are not displayed repeatedly, so they add up to 3.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[H_insight]

Hi @v-yanjiang-msft 

How can you refrence columns in the IF functions as it shows as error? unless you want me to creat a calculated column?

Lost Product 3 = 
VAR _Period =
    DATESINPERIOD ( Data[Date], MAX ( Data[Date] ), -3, MONTH )
RETURN
IF( 'Data'[Product] = "Production" && Data[Action] = "View" && NOT('Data'[Date]) IN _Period,1,
    IF( 'Data'[Product] = "Production" && Data[Action] = "View",0,BLANK()))

[v-yanjiang-msft · Accepted solution]

Hi @H_insight ,

In my sample, Lost Product is a calculated column, I also create a measure in my sample, it cannot get the expected result, I attach it bellow to help you understand.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[H_insight]

Hi @v-yanjiang-msft ,

Thanks again for your attempt, much appreciated. I realised the measure won't get me the expected result. For now I will accept this as a soultion.

Thanks, H

[smpa01]

@H_insight  Did you need this?

Lost Product = 
VAR _Period =
    DATESINPERIOD ( Data[Date], MAX ( Data[Date] ), -3, MONTH )
RETURN
    CALCULATE (
        COUNTROWS ( Data ),
        NOT ( Data[Date] ) IN _Period,
        FILTER ( Data, Data[Product] = "Production" && Data[Action] = "View" )
    )

[H_insight]

Hi @smpa01 

It works fine, but when I drag the date column nothing shows, any idea?

Thanks, H

[smpa01]

@H_insight  can you please provide your desired output clearly?

[H_insight]

Expected outcome like this:

with the new measure the output shows the correct result (4 records) but when I add "Date" column, nothing shows in PBI.