the number of values of the last date

[Datanik]

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 = 
    ADDCOLUMNS(
        sales,
        "maxdatefilter", 
        CALCULATE(MAX(sales[color_id]), FILTER(sales, sales[date] = maxdate)))
return
 
COUNTX(filtertable,[maxdatefilter])

 But I need to get

Please, I would be glad to help you in any way

[Anonymous]

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)

[Datanik]

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 · Accepted solution]

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]

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?

[Datanik]

made it with hands.

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

[johnt75]

Try

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

[Datanik]

I'm sorry, I see a different result