I want to hightlights new product in my table that never been sold before in previous month.
Here is my data example.
| DATE | Product |
|---|---|
| 1/1/2024 | carrot |
| 1/1/2024 | cabbage |
| 1/1/2024 | grape |
| 1/2/2024 | carrot |
| 1/2/2024 | cabbage |
| 1/2/2024 | grape |
| 1/2/2024 | mango |
| 1/3/2024 | carrot |
| 1/3/2024 | orange |
| 1/3/2024 | grape |
| 1/3/2024 | mango |
| 1/4/2024 | carrot |
| 1/4/2024 | orange |
| 1/4/2024 | grape |
| 1/4/2024 | lemon |
In Power BI, I create a table from visualisation and a slicer for fiter Date. I want to hightlights new product in my table that never been sold before in previous month. Then if I select date 1/2/2024 the table should show as below with hightlight at “mango”.
| DATE | Product |
|---|---|
| 1/2/2024 | carrot |
| 1/2/2024 | cabbage |
| 1/2/2024 | grape |
| 1/2/2024 | mango |
if I select date 1/3/2024 the table should show as below with hightlight at “orange”.
| DATE | Product |
|---|---|
| 1/3/2024 | carrot |
| 1/3/2024 | orange |
| 1/3/2024 | grape |
| 1/3/2024 | mango |
if I select date 1/4/2024 the table should show as below with hightlight at “Lemon”.
| DATE | Product |
|---|---|
| 1/4/2024 | carrot |
| 1/4/2024 | orange |
| 1/4/2024 | grape |
| 1/4/2024 | lemon |
Please show me how i can create function for formatting my table.
Thank you
**** My data was import from sql database with DirectQuery mode. Pleas consider this for DAX function if it can be use ****
Febb is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
You can use PREVIOUSMONTH to help with this.
Try a measure similar to this:
New Product =
var curProducts = DISTINCT(YourTable[Product])
var prevMonthProducts =
CALCULATETABLE(
DISTINCT(YourTable[Product]),
PREVIOUSMONTH(YourTable[DATE]) // or use your Date/Calendar table if you have one
)
var result = COUNTROWS( INTERSECT( curProducts, prevMonthProducts ))
return IF(result > 0, "", 1)
To give you a result of:
You can then use this measure in Conditional Formatting