Display latest value for a group using a date filter

[TVI7]

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 latest value for each inventory product every time it moves (sold, purchased etc).

Not all inventory items move on a given date.

So the last record of each inventory product represents the current value of the Product.

I'm trying to display a matrix visual that allows for a date to be passed to the visual and the latest value (before the date given by the filter) for each inventory product to be displayed.

I've tried many solutions offered by users on here, but I can't quite seem to nail it!

Here is some sample data (date format is dd/mm/yyyy).

Site will be used as a page filter to group the results by site.

So it's really just the last value for each Inventory product using a date passed to the visual. 

InventoryProduct	Site	Date	Value
1	1	1/02/2020	15
2	2	25/01/2020	18
3	1	2/02/2020	16
4	2	27/01/2020	5
1	1	5/02/2020	14
3	1	5/02/2020	17
2	2	3/02/2020	20
4	2	10/02/2020	19
1	1	10/02/2020	18
1	1	11/02/2020	1
2	2	11/02/2020	0
3	1	12/02/2020	12
4	1	14/02/2020	20
1	1	15/02/2020	12
2	2	15/02/2020	5

Thanks in advance!

[v-juanli-msft]

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]

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.

[amitchandak]

@TVI7 , Assuming the site is filtered

Measure = 
VAR __id = MAX ( 'Table'[InventoryProduct] )
VAR __date = CALCULATE ( MAX( 'Table'[Date] ), ALLSELECTED ( 'Table' ),  'Table'[InventoryProduct] = __id ) 
RETURN CALCULATE ( sum ( 'Table'[Value] ), VALUES ( 'Table'[InventoryProduct ), 'Table'[InventoryProduct] = __id, 'Table'[Date] = __date )

Use this measure in visual

[TVI7]

Thanks @amitchandak 

That has resolved the lines perfectly, however the totals of the column (either grouped or flat) are listing as zero.

Have double checked the data types and confirmed they are set to Currency for the Value column.

Yet the total remains at 0.00.

[amitchandak]

@TVI7 ,Try this on old measure

Measure 2=
sumx(summarize(Table,Table[InventoryProduct],Table[Date],"_1", [Measure]),[_1])