DAX Function / Query to create a flag for products

[hassanh2]

Hello,

Can anyone help with the following:

I have 2 tables (Item Master and Sales) and want to create a column/measure in the sales table to flag the products according to the following logic:

Based on a "Current Year" (Parameter / filter) the flag value for an Item/Product is:

- "New" - If product is sold in "Current Year" only

- "Existing" - If Product is sold in "Current Year" and in "Prior Year" (Current year -1)

- "No" - If Product was sold only in "Prior Year"

Thanks

[hassanh2]

That make sense! 

One more question I have here, is it possible apply this logic by adding additional dimention(s). In other words the flag should work for example on "Product" and "Region"?

[daXtreme]

@hassanh2 

If you say that you want to have a parameter for Current Year, then it has to be a measure out of necessity. Values in base tables cannot be changed once they've been refreshed. Also, if you want to stay sane and obtain a good model, you have to have at least 3 tables. One where you'll store your products, one which will be a Date table (Calendar) and one fact table storing the transactions. Without such a setup you'll be in trouble. But it's up to you, of course 🙂

[hassanh2]

You are absolutely right. I do have Date (Calendar) tabel which will be used to define Current year filter. I was just looking for the logic Item/Product Master and fact table.

[ribisht17 · Accepted solution]

@hassanh2 

Step1

Latest Order Year = CALCULATE(MAX(Sales[Year]),ALLEXCEPT(Sales,Sales[Product Name]))

Step2

Count of Orders = CALCULATE(DISTINCTCOUNT(Sales[Year]),ALLEXCEPT(Sales,Sales[Product Name]),Sales[Year]>=year(TODAY())-1)

Switch Flag = SWITCH( TRUE(), [Count of Orders]=1&&[Latest Order Year]=year(TODAY()),"New" ,[Count of Orders]=1,"No",[Count of Orders]>1&&[Latest Order Year]=year(TODAY()),"Existing")

OR

Using Variable,

Switch Flag Parameter = var Latest_Order_Year = CALCULATE(MAX(Sales[Year]),ALLEXCEPT(Sales,Sales[Product Name]))

var Count_of_Orders = CALCULATE(DISTINCTCOUNT(Sales[Year]),ALLEXCEPT(Sales,Sales[Product Name]),Sales[Year]>=year(TODAY())-1)

return SWITCH( TRUE(), [Count of Orders]=1&&[Latest Order Year]=year(TODAY()),"New" ,[Count of Orders]=1,"No",[Count of Orders]>1&&[Latest Order Year]=year(TODAY()),"Existing")

Regards,

Ritesh

[hassanh2]

Thank you Ritesh - this was helpfull and very close to what Im looking for, however the switch flag value in last row in your screen shot should be "New" since "Year" and "Latest Order Year" are both "2022". 

[ribisht17]

@hassanh2 

Did it help?

Please set the answer as Correct so that it can help others as well 

Regards,

Ritesh

[ribisht17]

That is Nokia which was there in the last year as well(2nd row),so it should be existing ,right ?

[ribisht17]

Mark my post as a solution if it helped you| Munde and Kudis (Ladies and Gentlemen) I like your Kudos!! !!
My YT Channel Dancing With Data !! Connect on Linkedin !!Power BI for Tableau Users