Last Record Value by Condition

[inversia]

Hi,

thank you for support.

To simplify, I have a fact Table A linked to the DATA Table.

DATA	COD	TYPE	AMOUNT
2024-11-20	A	F	0
2024-11-20	A	G	15
2024-10-02	A	F	10
2024-10-03	B	G	5
2024-10-01	B	F	1

I would like to get only the last value (ALL TIME) with TYPE = F and AMOUNT > 0, independently from the date selected in the filter menu :

COD	LAST VALUE
A	10
B	1
TOTAL	11

Thank you so much,

Alessandro

[FreemanZ]

hi @inversia ,

try like:

measure =

VAR _table =

ADDCOLUMNS(

   VALUES(data[COD]),

    "MinDate", CALCULATE(MIN(data[date])),

)

VAR _result =

CALCULATE(

    SUM(data[Amount]),

    TREATAS(

         _table,

         data[COD],

         data[date]

     ),

    data[TYPE]="F",

    data[amount]>0

)

RETURN _result

[inversia]

Thank you so much

[dharmendars007]

Hello @inversia , 

To calculate the "LAST VALUE" with the conditions specified (TYPE = F, AMOUNT > 0, independently of date filters), you can create a DAX measure in Power BI

LAST VALUE =
VAR LastDate =
CALCULATE( MAX('DATA'[DATA]),
FILTER(
ALL('DATA'), 'DATA'[TYPE] = "F" && 'DATA'[AMOUNT] > 0 && 'DATA'[COD] = MAX('DATA'[COD])))
VAR LastValue =
CALCULATE( MAX('DATA'[AMOUNT]),
FILTER( ALL('DATA'), 'DATA'[TYPE] = "F" && 'DATA'[AMOUNT] > 0 && 'DATA'[COD] = MAX('DATA'[COD]) &&
'DATA'[DATA] = LastDate))
RETURN
LastValue

This approach ensures that you retrieve the correct last value for each COD, even when the visual or report has date slicers applied

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[inversia · Accepted solution]

Hi @dharmendars007 ,

thank you so much.

For each COD the value is correct, but the total is wrong, maybe it get the MAX 

have you other idea? 

It might be useful create a table like and then make SUM(LastValue) ?

DATA	COD	TYPE	AMOUNT	LAST VALUE
2024-11-20	A	F	0
2024-11-20	A	G	15
2024-10-02	A	F	10	10
2024-10-03	B	G	5
2024-10-01	B	F	1	1

[dharmendars007]

Hello @inversia , 

To Display the Total: If you want to display the total of the "LAST VALUE" column (e.g., TOTAL 11), you can create another measure:

TOTAL LAST VALUE =
SUMX(VALUES('DATA'[COD]), [LAST VALUE])

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[inversia]

It's work. Awesome. Thank you so much