New Items

[Anonymous]

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?

[v-rzhou-msft]

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]

@v-rzhou-msft 

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

[v-rzhou-msft]

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]

Hi @v-rzhou-msft 

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

[v-rzhou-msft]

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]

@v-rzhou-msft 

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]

@v-rzhou-msft 

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.

[edhans]

Can you provide some sample data so we don't have to make something up that doesn't actually fit your request? See links below for how to do that.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

@edhans 

Here it is....

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

1	77419.92	SALES	CUR	LATEST 04 WKS
1	357.09	SALES	YAG	LATEST 04 WKS
1	173597.4	SALES	CUR	LATEST 13 WKS
1	107383.2	SALES	YAG	LATEST 13 WKS
1	297050.3	SALES	CUR	LATEST 26 WKS
1	140625.6	SALES	YAG	LATEST 26 WKS
1	305715.5	SALES	CUR	LATEST 52 WKS
1	159647	SALES	YAG	LATEST 52 WKS
1	21564	UNITS	CUR	LATEST 04 WKS
1	123	UNITS	YAG	LATEST 04 WKS
1	49824	UNITS	CUR	LATEST 13 WKS
1	34003	UNITS	YAG	LATEST 13 WKS
1	91030	UNITS	CUR	LATEST 26 WKS
1	45082	UNITS	YAG	LATEST 26 WKS
1	93350	UNITS	CUR	LATEST 52 WKS
1	51497	UNITS	YAG	LATEST 52 WKS
1	15.74	ACV MAX	CUR	LATEST 04 WKS
1	1.21	ACV MAX	YAG	LATEST 04 WKS
1	11.86	ACV MAX	CUR	LATEST 13 WKS
1	11.7	ACV MAX	YAG	LATEST 13 WKS
1	11.06	ACV MAX	CUR	LATEST 26 WKS
1	9.12	ACV MAX	YAG	LATEST 26 WKS
1	5.88	ACV MAX	CUR	LATEST 52 WKS
1	5.45	ACV MAX	YAG	LATEST 52 WKS

[amitchandak]

@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