Forum Discussion
Helper IDAX Function / Query to create a flag for products
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
Step1
Latest Order Year = CALCULATE(MAX(Sales[Year]),ALLEXCEPT(Sales,Sales[Product Name]))Step2Count 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
8 Replies
- ribisht17
Super User
Step1
Latest Order Year = CALCULATE(MAX(Sales[Year]),ALLEXCEPT(Sales,Sales[Product Name]))Step2Count 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
hassanh2Thank 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
That is Nokia which was there in the last year as well(2nd row),so it should be existing ,right ?
daXtremeSolution Sage
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.
- 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"?