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