cancel
Showing results for
Did you mean:
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

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors