Skip to main content
cancel
Showing results for 
Search instead 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

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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