Max date sales per product

[Anonymous]

i am trying to calculate total sales by max date and product. So when the date filter changes the value should shift to the new max date this needs to be done in a measure but when i currently use the following i get the correct number but whne put in a tbale with the products its the grand total of sales duplicated for each product

CALCULATE(SUM('table1'[value]),ALLEXCEPT('table1','table1'[product]),FILTER( ALL('table1'), 'table1'[Date] = Max('table1'[Date])))

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create measure

Measure =
VAR maxdate =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[product] = MAX ( 'Table'[product] ) )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[product] = MAX ( 'Table'[product] )
                && 'Table'[date] = maxdate
        )
    )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try like

sumx(values(Table[product]),lastnonblankvalue(Table[sales date],sum(Table[sales])))

[ryan_mayu]

@Anonymous 

Please share the sample data.

I think if you use date filter, you don't need to calculate the maxdate. The value will change by changing the date filter.

CALCULATE(SUM('table1'[value]),ALLEXCEPT('table1','table1'[product]))

[Anonymous]

@ryan_mayu  thank you but i need to as i only want the sum of the latest date i dont want the sum up to latest date

[ryan_mayu]

@Anonymous 

I am still not very clear about your scenario.

Show the latest date value by selection.

Measure = 
VAR maxdate=max('Table'[date])
return CALCULATE(sum('Table'[value]),'Table'[date]=maxdate)

Always show the latest date's value

Measure = 
VAR maxdate=CALCULATE(max('Table'[date]),all('Table'))
return CALCULATE(sum('Table'[value]),'Table'[date]=maxdate)