topic Re: Last Record Value by Condition in DAX Commands and Tips

Last Record Value by Condition

inversia — Fri, 29 Nov 2024 08:36:32 GMT

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

Re: Last Record Value by Condition

dharmendars007 — Fri, 29 Nov 2024 09:26:00 GMT

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

Re: Last Record Value by Condition

inversia — Fri, 29 Nov 2024 11:38:26 GMT

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

Re: Last Record Value by Condition

dharmendars007 — Fri, 29 Nov 2024 12:02:37 GMT

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

Re: Last Record Value by Condition

FreemanZ — Fri, 29 Nov 2024 14:56:13 GMT

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

Re: Last Record Value by Condition

inversia — Fri, 29 Nov 2024 15:19:54 GMT

It's work. Awesome. Thank you so much

Re: Last Record Value by Condition

inversia — Fri, 29 Nov 2024 15:20:28 GMT

Thank you so much