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 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. 

 

 

InventoryProductSiteDateValue
111/02/202015
2225/01/202018
312/02/202016
4227/01/20205
115/02/202014
315/02/202017
223/02/202020
4210/02/202019
1110/02/202018
1111/02/20201
2211/02/20200
3112/02/202012
4114/02/202020
1115/02/202012
2215/02/20205

 

Thanks in advance!

Need Help

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      TVI7
      Frequent Visitor
      6 years ago

      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's avatar
        amitchandak
        Super User
        6 years ago

        TVI7 ,Try this on old measure

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

  • 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.