I can't seem to upload images so here's the link: http://note.io/1SAhjjM

I need to get the last value of # Orders week avg in the current context (the highlighted value). The calendar dimension here is a week level one so I don't think I can use LASTNONBLANK. The measure is from the facts_sales table which is linked to a calendar dimension via a date key. 

Any ideas?

Thank you!

So the value highlighted is correct but you want some way of "grabbing" it to display it elsewhere? Not sure I understand.

That's right, that measure is calculated correctly, I just need to "grab" that value. I'm hoping to use the last available moving average as a "going forward" forecast for dates for which there are no actuals available. 

OK, so "# Orders avg week" is a measure/Calculated Field, not a Calculated Column, correct?

I'm asking because I've never had success with LASTNONBLANK using a Calculated Field but have with Calculated Columns.

This LASTNONBLANK also works with measures:

LastPriceMeasure:=CALCULATE([Sum_Value]; FILTER ('Table'; 'Table'[Date] =CALCULATE (LASTNONBLANK ('Table'[Date]; 1 );  FILTER ( 'Table'; 

https://onedrive.live.com/edit.aspx?cid=de165ddf5d02daff&page=view&resid=DE165DDF5D02DAFF!11856&parId=DE165DDF5D02DAFF!107&app=Excel&wacqt=undefined

Although the downside is that you have to work with a disconnected DimDate/Calendar table. I vaguely remember that I've seen approaches with connected DateTables, so maybe some googling in this direction would help.

Here is the full formula from that spreadsheet, it seems to have been cut off above:

LastPriceMeasure:=CALCULATE([Sum_Value], FILTER ('Table', 'Table'[Date] =CALCULATE (LASTNONBLANK ('Table'[Date], 1 ),  FILTER ( 'Table',

                     'Table'[Date] <=LASTDATE(DimDate[Date])))))

So, if I am reading this correctly, it is calculating the sum of value (Sum_Value) filtering Table by the [Date] column in Table which is being set equal to the result of the last non blank value of Table being filtered by [Date] < the last date of the date dimension.

So the last part returns all dates less than the last date of the date dimension and this would be done for each date in Table when used as a measure.

Then last non blank returns the last non blank date of the result above

Table is filtered by this date and the sum of value is calculated for that particular date...

Correct?

• Proposed as answer by Michael AmadiModerator Sunday, July 26, 2015 10:35 AM

Hi Seth,

thanks for the correction & yes, I think that's how it works.

If you come by a solution that works with connected tables, please share.