The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I need a solution with DAX in PowerBI. It may be calculated in a measure or new column.
Search for the last value (not BLANK) in the "VALUE2" column and return it in the "NewField" column.
WEEK | STATUS | VALUE1 | VALUE2 | NewField |
2023KW01 | Status2 | 2500 | 2500 | |
2023KW01 | Status1 | 75 | 2500 | |
2023KW03 | Status1 | 120 | 2500 | |
2023KW04 | Status1 | 200 | 2500 | |
2023KW05 | Status1 | 230 | 2500 | |
2023KW06 | Status1 | 280 | 2500 | |
2023KW06 | Status2 | 2700 | 2700 | |
2023KW07 | Status1 | 410 | 2700 | |
2023KW08 | Status1 | 460 | 2700 |
Many Thanks
sicos
Thanks for the solution. It only works if the value in the "VALUE" field is ascending. It should also work with ascending and descending values.
WEEK | STATUS | VALUE1 | VALUE2 | NewField |
2023KW01 | Status2 | 2500 | 2500 | |
2023KW01 | Status1 | 75 | 2500 | |
2023KW03 | Status1 | 120 | 2500 | |
2023KW04 | Status1 | 200 | 2500 | |
2023KW04 | Status1 | 200 | 2500 | |
2023KW04 | Status2 | 2600 | 2600 | |
2023KW06 | Status1 | 280 | 2600 | |
2023KW06 | Status2 | 2400 | 2400 | |
2023KW07 | Status1 | 410 | 2400 | |
2023KW08 | Status1 | 460 | 2400 |
Many Thanks
Try this one
NewField =
VAR CurrentWeek = 'YourTable'[WEEK]
VAR LastNonBlankValue =
CALCULATE(
LASTNONBLANK('YourTable'[VALUE2], 'YourTable'[VALUE2]),
NOT(ISBLANK('YourTable'[VALUE2])),
FILTER('YourTable', 'YourTable'[WEEK] = CurrentWeek)
)
RETURN
LastNonBlankValue
Proud to be a Super User! |
|
@sicos79 , Please try this method
NewField =
CALCULATE(
LASTNONBLANK('YourTable'[VALUE2], 1),
FILTER(
ALL('YourTable'),
'YourTable'[WEEK] <= EARLIER('YourTable'[WEEK]) &&
NOT(ISBLANK('YourTable'[VALUE2]))
)
)
Accept as solution and give kudos if it helps
Proud to be a Super User! |
|
User | Count |
---|---|
10 | |
9 | |
6 | |
6 | |
5 |
User | Count |
---|---|
20 | |
15 | |
14 | |
9 | |
7 |