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 =...
Need Help
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.