cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## the number of values of the last date

There is a very simple task, but I can't figure out how to do it.

I need to calculate how many colors of the product were sold on the last day of sale for each product.

table 1

table 2

my measure

``````lastColorSaleCount =
var  maxdate =    max(sales[date])

var filtertable =
sales,
"maxdatefilter",
CALCULATE(MAX(sales[color_id]), FILTER(sales, sales[date] = maxdate)))
return

COUNTX(filtertable,[maxdatefilter])``````

But I need to get

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hello,

i just changed Max(color_id) to Distinct(Color_id)

and then the measure:

Count of Color = Calculate(COUNTA(sales[Color per Max Date - Product]),filter(sales,Sales[Color per Max Date - Product]<>""))

7 REPLIES 7
Anonymous
Not applicable

hello, this is very lengthy but might help:

(i used calculated column)

Color per Max Date - Product =
var product_id = sales[id]
var color_id = sales[color_id]
var salesdate = sales[date]

var maxSales = calculate(max(sales[date]),filter(sales,product_id=sales[id]))

return if(sales[date] = maxSales,calculate(max(Color[id]),filter(all(sales),sales[id]=product_id && sales[date] = maxSales)),0)

and then you can add a measure counting the colors:
Count of Color = Calculate(count(sales[Color per Max Date - Product]),sales[Color per Max Date - Product]<>0)

Frequent Visitor

Hi, thanks for the help, but it only works on numeric fields. i made a simplified model, mine is much more complicated and my mistake is that i made it very simple. please help me again

Anonymous
Not applicable

Hello,

i just changed Max(color_id) to Distinct(Color_id)

and then the measure:

Count of Color = Calculate(COUNTA(sales[Color per Max Date - Product]),filter(sales,Sales[Color per Max Date - Product]<>""))

Anonymous
Not applicable

hello, are you trying to get which products and how many were sold on the latest date on your table? also could you explain how did you come up with the screenshot (the supposedly result) so we can help you out more?

Frequent Visitor

I'm looking for what colors were sold on the last dates
product 1 = color 2
product 2 = color 3
product 3 = color 2

total
color 2 - 2
color 3 - 1

Super User

Try

``````Sales on last day =
SUMX (
'Product',
VAR LastSaleDate =
CALCULATE ( MAX ( 'Sales'[Sale date] ) )
RETURN
CALCULATE ( COUNTROWS ( 'Sales' ), 'Sales'[Sale date] = LastSaleDate )
)
``````
Frequent Visitor

I'm sorry, I see a different result