Conversion of a calculated column to measure

[Chimsie]

I'm trying to convert a calculated column into a measure so that I am able to use the measure with a slicer,
I want to apply the slicer to the column Forecast Reliability Score. Depending on the selected Forecast Reliability Score (multi-select), the value in column kW Predicted may or may not be added to the total LASTNONBLANK.

LASTNONBLANK = 
var CA = [CA]
var curDate = [Date] 
var priorMaxDate = CALCULATE(MAX('Forecast Trendline'[Date]), FILTER('Forecast Trendline', 'Forecast Trendline'[CA] = CA && [Date] < curDate && 'Forecast Trendline'[Value] <> BLANK())) 
var sum_pv = SUMX(FILTER('Forecast Trendline','Forecast Trendline'[CA]=CA&&'Forecast Trendline'[Date]<=curDate),'Forecast Trendline'[kW Predicted])
return 
if([Value] = blank(), sum_pv+CALCULATE(max('Forecast Trendline'[Value]), FILTER('Forecast Trendline', [CA] = CA && [Date] = priorMaxDate)), 'Forecast Trendline'[Value])

Table:

CA	Date	LASTNONBLANK	KW Predicted	Value	Forecast Reliability Score
CA414	Tuesday, December 15, 2020	101.713		101.713
CA415	Tuesday, December 15, 2020	100.036		100.036
CA414	Wednesday, December 16, 2020	101.548		101.548
CA415	Wednesday, December 16, 2020	100.603		100.603
CA414	Thursday, December 17, 2020	101.266		101.266
CA415	Thursday, December 17, 2020	100.31		100.31
CA415	Saturday, December 19, 2020	112.31	12		A - Confirmed by source
CA415	Wednesday, March 3, 2021	114.31	2		A - Confirmed by source
CA415	Tuesday, March 16, 2021	119.31	5		C - Undetermined
CA415	Tuesday, June 1, 2021	114.81	-4.5		B - Draft
CA415	Wednesday, June 2, 2021	105.81	-9		B - Draft
CA414	Thursday, June 3, 2021	85.166	-16.1		A - Confirmed by source
CA415	Friday, June 4, 2021	103.81	-2		B - Draft

 
On requested I've added additional info:

PBIX file as example. PBIX File

[d_gosbell · Accepted solution]

So the following slight alteration to your column expression should allow it to work as a measure

LASTNONBLANK 2 = 
var CA = SELECTEDVALUE('Forecast Trendline'[CA])
var curDate = SELECTEDVALUE('Forecast Trendline'[Date] )
var priorMaxDate = CALCULATE(MAX('Forecast Trendline'[Date])
      , FILTER(ALL('Forecast Trendline')
          , [Date] < curDate 
          && 'Forecast Trendline'[Value] <> BLANK() 
          && 'Forecast Trendline'[CA] = CA)) 
var sum_pv = CALCULATE(
      SUM('Forecast Trendline'[kW Predicted])
      , 'Forecast Trendline'[Date]<=curDate)
return if(ISBLANK(SUM('Forecast Trendline'[Value]) )
    , sum_pv+ CALCULATE(max('Forecast Trendline'[Value]), 'Forecast Trendline'[Date] = priorMaxDate)
    , sum('Forecast Trendline'[Value]))

[Chimsie]

@d_gosbell  Thank you very much, this solved my issue.

Christmas is early this year 🙂

[Chimsie]

@amitchandak @AllisonKennedy 

I've attached a PBIX file as example. PBIX File

Additional explanation:
Column CA: Physical Enviroment
Column Value: Is daily measured kilowatt (kW) data
Column Kw Predicted: Predicted grow in kW in the future
Column Project Reliabilty Score: The Reliabilty of the Kw Predicted grow
Column LASTNONBLANK: is the SUM of Value and Kw Predicted.

What I want to achieve is that I can use a Slicer to filter on the Project Reliabilty Score,

So when I uncheck Undertermined in the slicer the line chart will filter the Project Reliabilty Score: Undertermined and re-calculate the line chart without the prediceted value from Undertermined.

As you can see, when I uncheck undertermined, the calculated value stay the same, 101. 

[amitchandak]

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

Try like

LASTNONBLANK =
var CA = max('Forecast Trendline'[CA] )
var curDate = max([Date])
var priorMaxDate = CALCULATE(MAX('Forecast Trendline'[Date]), FILTER('Forecast Trendline', 'Forecast Trendline'[CA] = CA && [Date] < curDate && 'Forecast Trendline'[Value] <> BLANK()))
var sum_pv = SUMX(FILTER('Forecast Trendline','Forecast Trendline'[CA]=CA && 'Forecast Trendline'[Date]<=curDate),'Forecast Trendline'[kW Predicted])
return
if( isblank([Value]), sum_pv+CALCULATE(max('Forecast Trendline'[Value]), FILTER('Forecast Trendline', [CA] = CA && [Date] = priorMaxDate)), 'Forecast Trendline'[Value])

[Chimsie]

The calculation returns a error, I've attached a pbix sample file.

Could have a look at this?

[AllisonKennedy]

@Chimsie Sorry, I don't understand your question. Can you provide a sample of what you're looking for as final result?