Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
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?
Thanks in advance!

Newuser13_0-1685435705104.png

 

3 REPLIES 3
NaveenGandhi
Super User
Super User

Hello @Anonymous 

 

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

 

Regards,

Naveen

Anonymous
Not applicable

Attaching sample dataset:

YearMonthDateCurrencyPriceProduct 
202211-Jan-22USD1AAA
202221-Feb-22USD1AAA
202221-Feb-22AED54AAA
202221-Feb-22AED54AAA
202231-Mar-22AED54AAA
202231-Mar-22AED54AAA
202241-Apr-22USD1AAA
202241-Apr-22USD1AAA
202251-May-22USD12AAA
202251-May-22USD1AAA
202251-May-22AED48.6AAA
202261-Jun-22USD1AAA
202271-Jul-22USD1AAA
202271-Jul-22AED54AAA
202271-Jul-22AED54AAA
202271-Jul-22USD1AAA
202281-Aug-22USD1AAA
2022101-Oct-22USD12AAA
2022101-Oct-22AED54AAA
2022111-Nov-22USD1AAA

 

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
NaveenGandhi_0-1685443829666.png

 

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.