Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
All,
I need help with a LASTNONBLANK formula, I want the output of the formula to sum the value from the column "Predicted Value"
in the column LASTNONBLANK
The formula:
LASTNONBLANK = |
Example:
Thanks!
Solved! Go to Solution.
Hi @Chimsie ,
See if this will meet your needs:
LASTNONBLANK =
var CA = [CA]
var curDate = [Date]
var priorMaxDate = CALCULATE(MAX('AssetRealtimeDataLog'[Date]), FILTER('AssetRealtimeDataLog', 'AssetRealtimeDataLog'[CA] = CA && [Date] < curDate && 'AssetRealtimeDataLog'[Value] <> BLANK()))
var sum_pv = SUMX(FILTER(AssetRealtimeDataLog,AssetRealtimeDataLog[CA]=CA&&AssetRealtimeDataLog[DATE]<=curDate),AssetRealtimeDataLog[PREDICTED VALUE])
return
if([Value] = blank(), sum_pv+CALCULATE(max('AssetRealtimeDataLog'[Value]), FILTER('AssetRealtimeDataLog', [CA] = CA && [Date] = priorMaxDate)), 'AssetRealtimeDataLog'[Value])
Add a cumulative value of [PREDICTED VALUE] column before the return value.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Input | Input | Input | Calc | Input | Expected Result |
Date | CA | Value | LASTNONBLANK | Predicted value | RESULT |
Wednesday, November 25, 2020 | CA111 | 66.804 | 66.804 | 66.804 | |
Wednesday, November 25, 2020 | CA999 | 37.449 | 37.449 | 37.449 | |
Thursday, November 26, 2020 | CA111 | 65.852 | 65.852 | 65.852 | |
Thursday, November 26, 2020 | CA999 | 37.434 | 37.434 | 37.434 | |
Friday, November 27, 2020 | CA111 | 64.38 | 64.38 | 64.38 | |
Friday, November 27, 2020 | CA999 | 37.126 | 37.126 | 37.126 | |
Saturday, November 28, 2020 | |||||
Sunday, November 29, 2020 | |||||
Monday, November 30, 2020 | |||||
Tuesday, December 1, 2020 | CA111 | 64.38 | 20 | 84.38 | |
Tuesday, December 1, 2020 | CA999 | 37.126 | 30 | 67.126 | |
Wednesday, December 2, 2020 | |||||
Thursday, December 3, 2020 | |||||
Friday, December 4, 2020 | |||||
Saturday, December 5, 2020 | CA111 | 64.38 | 30 | 114.38 | |
Sunday, December 6, 2020 | |||||
Monday, December 7, 2020 | |||||
Tuesday, December 8, 2020 | |||||
Wednesday, December 9, 2020 | |||||
Thursday, December 10, 2020 | CA111 | 64.38 | 10 | 124.38 | |
Thursday, December 27, 2020 | CA999 | 37.126 | 20 | 104.38 |
@Chimsie ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
@amitchandak see attached sample data, the Column LASTNONBLANK is based on the formula above, the RESULT column is the result where I'm looking for, preferably in the Column LASTNONBLANK.
The RESULT column is the LASTNONBLANK and Predicted Value Added (SUM) column together and used as input as for the next LASTNONBLANK. I hope it makes sense what I'm asking.
I was not able to past a table in to the message, the system keeps complaining about a HTML error in the message header, is a picture also oke?
Hi @Chimsie ,
See if this will meet your needs:
LASTNONBLANK =
var CA = [CA]
var curDate = [Date]
var priorMaxDate = CALCULATE(MAX('AssetRealtimeDataLog'[Date]), FILTER('AssetRealtimeDataLog', 'AssetRealtimeDataLog'[CA] = CA && [Date] < curDate && 'AssetRealtimeDataLog'[Value] <> BLANK()))
var sum_pv = SUMX(FILTER(AssetRealtimeDataLog,AssetRealtimeDataLog[CA]=CA&&AssetRealtimeDataLog[DATE]<=curDate),AssetRealtimeDataLog[PREDICTED VALUE])
return
if([Value] = blank(), sum_pv+CALCULATE(max('AssetRealtimeDataLog'[Value]), FILTER('AssetRealtimeDataLog', [CA] = CA && [Date] = priorMaxDate)), 'AssetRealtimeDataLog'[Value])
Add a cumulative value of [PREDICTED VALUE] column before the return value.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
See if the article here helps - LASTNONBLANK Explained - Excelerator BI
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |