Forum Discussion

H_insight's avatar
H_insight
Icon for Helper V rankHelper V
4 years ago
Solved

Lost Product

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

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

8 Replies

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      H_insight
      Icon for Helper V rankHelper V
      4 years ago

      Hi smpa01 

       

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

       

      Thanks, H

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      H_insight
      Icon for Helper V rankHelper V
      4 years ago

      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's avatar
        v-yanjiang-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        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.