Current and LASTNONBLANK results

Question

Hello,&nbsp;I have this measure which gives me the percentage results for the currently filtered months:&nbsp;overall_% = SELECTEDVALUE(a_fact_results[no_responses])/SELECTEDVALUE(a_fact_results[base])I want to display the previous LASTNONBLANK results in a new column. How can I alter the code to display this?&nbsp;&nbsp;&nbsp;

anonymous · Answer

Previous_LastNonBlank_Results = CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LASTNONBLANK(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;a_fact_results[no_responses], a_fact_results[month]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; a_fact_results,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; a_fact_results[month] &lt; SELECTEDVALUE(a_fact_results[month])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ))

123abc · Answer

To display the previous LASTNONBLANK results in a new column, you can create a calculated column in your Power BI model. You'll need to use a combination of DAX functions to achieve this. Here's a modified version of your DAX measure that calculates the previous LASTNONBLANK result in a new column:

previous_LASTNONBLANK_result =
VAR CurrentMonth = MAX(a_fact_results[MonthColumn]) -- Replace 'MonthColumn' with the name of your date or month column
VAR PreviousMonth = CALCULATE(MAX(a_fact_results[MonthColumn]), FILTER(ALL(a_fact_results), a_fact_results[MonthColumn] < CurrentMonth))
RETURN
IF(ISBLANK(PreviousMonth), BLANK(),
CALCULATE(
[overall_%],
FILTER(a_fact_results, a_fact_results[MonthColumn] = PreviousMonth)
)
)

In this code:

VAR CurrentMonth retrieves the current month based on your filter context.
VAR PreviousMonth calculates the previous month by finding the maximum month value that is less than the current month.
The IF function checks if PreviousMonth is not blank. If it's blank, it returns blank. Otherwise, it calculates the [overall_%] measure for the previous month using the FILTER function to set the filter context to that month.

Replace MonthColumn with the actual name of the column that represents your months or dates.

You can create a new calculated column in your Power BI model and use this DAX formula. It will calculate the previous LASTNONBLANK result for each row based on the filter context of the current row.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

mbug123 · Answer

Many thanks&nbsp;123abc&nbsp;!I input your code and got an error message because 'PreviousMonth' is a reserved term. I changed it to 'ThePreviousMonth'. This gave me a response, unfortunately, all responses were 30 December 1899 (which I assume is the earliest date.&nbsp;Would you be able to modify your code to make it give the correct answer, as I feel like its almost there?&nbsp;surveydate column displays the full date of the last survey, e.g. 28/07/2019previous_LASTNONBLANK_result = VAR CurrentMonth =&nbsp; &nbsp; MAX ( a_fact_results[survey_date] )VAR ThePreviousMonth =&nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; MAX ( a_fact_results[survey_date] ),&nbsp; &nbsp; &nbsp; &nbsp; FILTER ( ALL ( a_fact_results ), a_fact_results[survey_date] &lt; CurrentMonth )&nbsp; &nbsp; )RETURN&nbsp; &nbsp; IF (&nbsp; &nbsp; &nbsp; &nbsp; ISBLANK ( ThePreviousMonth ),&nbsp; &nbsp; &nbsp; &nbsp; BLANK (),&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [overall_%],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER ( a_fact_results, a_fact_results[survey_date] = ThePreviousMonth)&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )

mbug123 · Answer

Many thanks for your code. Unfortunately, I got an error message because it was expecting a measure here for some reasonPrevious_LastNonBlank_Results = CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LASTNONBLANK(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;a_fact_results[no_responses], a_fact_results[month]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; a_fact_results,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; a_fact_results[month] &lt; SELECTEDVALUE(a_fact_results[month])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ))

Forum Discussion

Current and LASTNONBLANK results

4 Replies

Related content

Lastnonblank not returning correct results

LastNonBlank Value

conditional lastnonblank

Incorrect totals with LASTNONBLANK.

Download cypher results