Forum Discussion
Last Record Value by Condition
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
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
6 Replies
Thank you so much
- dharmendars007
Memorable Member
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
LastValueThis 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
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
