Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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!

 

Need Help

3 Replies

  • NaveenGandhi's avatar
    NaveenGandhi
    Memorable Member
    3 years ago

    Hello Anonymous 

     

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

     

    Regards,

    Naveen

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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

       

      • NaveenGandhi's avatar
        NaveenGandhi
        Memorable Member
        3 years ago

        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.