Forum Discussion

TVI7's avatar
TVI7
Frequent Visitor
6 years ago

Display latest value for a group using a date filter

Hi All, Hoping someone can help! New to DAX / M (more of a SQL guy!)   This should be easy....but i just cant seem to find the right syntax!   I have an audit table that always generates the la...
Need Help
v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

Hi TVI7 

Please check if the following helps you.

Create measures

Measure =
VAR lastdate1 =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[InventoryProduct]
                = MAX ( 'Table'[InventoryProduct] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[InventoryProduct]
                = MAX ( 'Table'[InventoryProduct] )
                && 'Table'[Date] = lastdate1
        )
    )

total_sum = IF(ISINSCOPE('Table'[InventoryProduct]),[Measure],SUMX(ALL('Table'[InventoryProduct]),[Measure]))

 

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.

TVI7's avatar
TVI7
Frequent Visitor
6 years ago

Thanks v-juanli-msft amitchandak ,

 

Both solutions don't give me quite what i'm looking for.

In reality, i need to find the highest record (row) when given a date.

 

This will be grouped in the visual by the Site & inventoryproduct.

so if i was to add a date filter to the visual for '10/2/2020', i'd get the max record for each inventoryproduct before that date.

I've tried EARLIEST functions (i have an Index on the data), but can't seem to make it work.