Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Datanik
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

Datanik_3-1682347772931.png

 

table 2

Datanik_2-1682347755520.png

 

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])

 

 

 

Datanik_4-1682347956938.png

 But I need to get

Datanik_5-1682348160197.png

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



 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hello, 

JekA_0-1682418318248.png

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]<>""))



View solution in original post

7 REPLIES 7
Anonymous
Not applicable

hello, this is very lengthy but might help:

JekA_0-1682355719232.png

(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)

JekA_1-1682355833854.png

 



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

Datanik_0-1682409604009.png

 

Anonymous
Not applicable

Hello, 

JekA_0-1682418318248.png

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?

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
Super User
Super User

Try

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

Datanik_0-1682351405905.png

I'm sorry, I see a different result

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors