Lastnonblankvalue calculation problem

[Anonymous]

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!

[NaveenGandhi]

Hello @Anonymous 

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

Regards,

Naveen

[Anonymous]

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

[NaveenGandhi]

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.