cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Lastnonblankvalue calculation problem

Hi All,

I'm having a problem with creating a measure to show in all blank spaces price from the last available month.
Currecntly I have a measure like this:

Price New =
VAR vThisDate =
MAX ( RSP_Calc[Date] )

VAR vLastDate =
IF(vThisDate=BLANK(),CALCULATE(LASTNONBLANKVALUE(RSP_Calc[Date],MAX(RSP_Calc[Price]))),MAX(RSP_Calc[Price]))

RETURN
vLastDate

If a change this part of measure 'CALCULATE(LASTNONBLANKVALUE(RSP_Calc[Date],MAX(RSP_Calc[Price])))' to e.g. 100 it shows 100 in all blanks spaces. So something is not correct with this part.

Does anybody know the answer?

3 REPLIES 3
Super User

Hello @Anonymous

Kindly provide sample data or PBIX to understand the problem better.

Regards,

Naveen

Anonymous
Not applicable

Attaching sample dataset:

 Year Month Date Currency Price Product 2022 1 1-Jan-22 USD 1 AAA 2022 2 1-Feb-22 USD 1 AAA 2022 2 1-Feb-22 AED 54 AAA 2022 2 1-Feb-22 AED 54 AAA 2022 3 1-Mar-22 AED 54 AAA 2022 3 1-Mar-22 AED 54 AAA 2022 4 1-Apr-22 USD 1 AAA 2022 4 1-Apr-22 USD 1 AAA 2022 5 1-May-22 USD 12 AAA 2022 5 1-May-22 USD 1 AAA 2022 5 1-May-22 AED 48.6 AAA 2022 6 1-Jun-22 USD 1 AAA 2022 7 1-Jul-22 USD 1 AAA 2022 7 1-Jul-22 AED 54 AAA 2022 7 1-Jul-22 AED 54 AAA 2022 7 1-Jul-22 USD 1 AAA 2022 8 1-Aug-22 USD 1 AAA 2022 10 1-Oct-22 USD 12 AAA 2022 10 1-Oct-22 AED 54 AAA 2022 11 1-Nov-22 USD 1 AAA

Super User

Hello @Anonymous

Create a calendar table with a one to many relationship with your fact table. Then use the below dax.

Price New =

VAR vLastDate =
if(max('Blank Price'[Price])=BLANK(),CALCULATE(max('Blank Price'[Price]),PREVIOUSMONTH('Calendar'[Date])))

RETURN
vLastDate

Let me know if this helps.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly.