Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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
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.
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.
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?
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.
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
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 |
@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