Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

New Items

Hi,

 

I have data that has UPCs and sales data for them. My data is structured in a way that I have 4 times for data. Latest 4 weeks, latest 13 weeks, latest 26 weeks and latest 52 weeks and for each of the time frames I have a CUR and YAG time frame.

 

My sales measure is: 

SALES - CUR = CALCULATE ( [CUR], 'tffacts'[MEASURE] = "SALES" )
 
I would like to be able to identify those UPCs that appear in the lastest 4 weeks for the first time and flag them as "new" items.
 
I refresh this data every 4 weeks.
 
Any ideas?

 

 

Need Help

10 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      edhans 

       

      Here it is....

       

      I am guessing that "New" would be when latest 4, 13, 25 adn 52 weeks sales are indential?

       

      177419.92SALESCURLATEST 04 WKS
      1357.09SALESYAGLATEST 04 WKS
      1173597.4SALESCURLATEST 13 WKS
      1107383.2SALESYAGLATEST 13 WKS
      1297050.3SALESCURLATEST 26 WKS
      1140625.6SALESYAGLATEST 26 WKS
      1305715.5SALESCURLATEST 52 WKS
      1159647SALESYAGLATEST 52 WKS
      121564UNITSCURLATEST 04 WKS
      1123UNITSYAGLATEST 04 WKS
      149824UNITSCURLATEST 13 WKS
      134003UNITSYAGLATEST 13 WKS
      191030UNITSCURLATEST 26 WKS
      145082UNITSYAGLATEST 26 WKS
      193350UNITSCURLATEST 52 WKS
      151497UNITSYAGLATEST 52 WKS
      115.74ACV MAXCURLATEST 04 WKS
      11.21ACV MAXYAGLATEST 04 WKS
      111.86ACV MAXCURLATEST 13 WKS
      111.7ACV MAXYAGLATEST 13 WKS
      111.06ACV MAXCURLATEST 26 WKS
      19.12ACV MAXYAGLATEST 26 WKS
      15.88ACV MAXCURLATEST 52 WKS
      15.45ACV MAXYAGLATEST 52 WKS
      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Anonymous , Still not very clear to me

        sales = calculate(sum(Table[value]),Table[Meausre]= "SALES" ,Table[Type] ="LATEST 04 WKS")
        CUR = calculate(sum(Table[value]),Table[Meausre]= "CUR" ,Table[Type] ="LATEST 04 WKS")

        new measure =SALES - CUR

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous 

    Due to I don’t know what do the values like UPCS,SALES, UNITS ,CUR ,YAG mean, I use your value and build the column head by myself to have a test.

    Table:

    And I build a measure to find the value whose Status = Sales and Time frame = CUR and Date = LATEST 04 WKS to flag as “New”.

     

    New = IF( MAX('Table'[Status])="SALES"&&MAX('Table'[Time frame])="CUR"&&MAX('Table'[Date])="LATEST 04 WKS","NEW",BLANK())

     

    Result：

    And I build another sample for you which I hope it can help you.

    If this reply still couldn’t help you to solve this problem please provide me more details about your column headers and the meaning or each values like UPCS, CUR,YAG ACVMAX ,UNITS and I think there should exist value format like yyyy/mm/tt in your table you should show them to me, or you can provide me with your pbix file from your onedrive for business.

    You can download the pbix file from this link: New Items

     

    Best Regards,

    Rico Zhou

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous 

       

      That is close but not quite.

       

      If you think about it. If an item is "New" in the latest 4 weeks it means the sales of latest 4 weeks has to equal sales of latest 13,26 and 52 weeks.

       

      Can you adjust formula to the above statement?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Anonymous 

         

        I also didn't mention but m y sample data only has 1 UPC but there are actually hundreds of UPCs that this needs to calcuate against.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Anonymous 

       

      Were you able to look at my last responses to your solution?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Anonymous 

        Sorry for responing late. I build a new table2, and add some values in it.

        Then I build a new measure to achieve your goal:

         

        New 2 = 
VAR _Avg =
    CALCULATE (
        AVERAGE ( Table2[Amount] ),
        FILTER (
            ALL ( Table2 ),
            Table2[UPCS] = MAX ( Table2[UPCS] )
                && 'Table2'[Status]
            = MAX ( 'Table2'[Status] )
            && Table2[Time frame] = MAX ( 'Table2'[Time frame] )
    ))
VAR _Min =
    CALCULATE (
        MIN ( Table2[Amount] ),
        FILTER (
            ALL ( Table2 ),
            Table2[UPCS] = MAX ( Table2[UPCS] )
                && 'Table2'[Status]
            = MAX ( 'Table2'[Status] )
            && Table2[Time frame] = MAX ( 'Table2'[Time frame] )
    ))
RETURN
    IF (
        _Avg = _Min
            && MAX ( 'Table2'[Status] ) = "SALES"
            && MAX ( 'Table2'[Time frame] ) = "CUR"
            && MAX ( 'Table2'[Date] ) = "LATEST 04 WKS",
        "New",
        BLANK ()
    )

        This measure will mark the one whose 04 Amount = 13/26/52 Amount, Statues = Sales and Time Frame = Cur as New.

         

        You can download the pbix file from this link: New Items

         

        Best Regards,

        Rico Zhou

         

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi @gmooney10 

    Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.

     

    Best Regards,

    Rico Zhou

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous 

       

      I sent you a private message with a link to my file