cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

How to extent LASTNONBLANK formula with SUM

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:
Capture.PNG

Thanks!

 

1 ACCEPTED SOLUTION

Hi @Chimsie ,

 

See if this will meet your needs:

V-lianl-msft_0-1606721702250.png

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])

V-lianl-msft_1-1606721751990.png

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.

View solution in original post

6 REPLIES 6
Helper II
Helper II

 

 

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

 

 

Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

Chimsie_0-1606515713514.png

 

Hi @Chimsie ,

 

See if this will meet your needs:

V-lianl-msft_0-1606721702250.png

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])

V-lianl-msft_1-1606721751990.png

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.

View solution in original post

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors