cancel
Showing results for
Did you mean:
Helper II

## Conversion of a calculated column to measure

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

PBIX file as example. PBIX File

1 ACCEPTED SOLUTION
Super User II

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]))``````
6 REPLIES 6
Super User II

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]))``````
Helper II

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

Christmas is early this year 🙂

Helper II

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

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.

Super User IV

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

Proud to be a Super User!

Helper II

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

Could have a look at this?

Super User III

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

?? Check out my March Madness Report??

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.

www.excelwithallison.com

Announcements