How to extent LASTNONBLANK formula with SUM

[Chimsie]

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 = 
var CA = [CA]
var curDate = [Date] 
var priorMaxDate = CALCULATE(MAX('AssetRealtimeDataLog'[Date]), FILTER('AssetRealtimeDataLog', 'AssetRealtimeDataLog'[CA] = CA && [Date] < curDate && 'AssetRealtimeDataLog'[Value] <> BLANK())) 
return 
if([Value] = blank(), CALCULATE(max('AssetRealtimeDataLog'[Value]), FILTER('AssetRealtimeDataLog', [CA] = CA && [Date] = priorMaxDate)), 'AssetRealtimeDataLog'[Value])

Example:


Thanks!

[Chimsie]

 

 

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

 

 

[amitchandak]

@Chimsie ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Chimsie]

@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?

[V-lianl-msft · Accepted 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.

[Chimsie]

@V-lianl-msft Thank You very much, this solved my issue:-)

[Ashish_Mathur]

Hi,

See if the article here helps - LASTNONBLANK Explained - Excelerator BI