Replicate same value in all month where revenue is blank till we have new non blank revenue.

[Anonymous]

Need to replicate 89.23 in all months

Price Index = CALCULATE(Price_Index_Query[Price_Index_Working],DATEADD('Calendar'[Date],-1,MONTH))

This is the DAX I am currently using it replicates only for one month needs to replicate till we find a value in invoice revenue.

[DallasBaba · Accepted solution]

@Anonymous you can do this by creating a calculated column  

Revenue_Filled =
VAR _currentDate = MAX('Calendar'[Date])
VAR _lastNonBlankDate =
CALCULATE(
MAX('Calendar'[Date]),
FILTER(
ALL('Calendar'),
'Calendar'[Date] <= _currentDate &&
NOT(ISBLANK(Price_Index_Query[Price_Index_Working]))
)
)
RETURN
CALCULATE(
MAX(Price_Index_Query[Price_Index_Working]),
FILTER(
ALL('Calendar'),
'Calendar'[Date] = _lastNonBlankDate
)
)

This formula works by finding the last date that has a non-blank revenue value for each row, and then returning the revenue value for that date. This way, the blanks are filled with the previous non-blank value until a new non-blank value is encountered.

Let me know is this work
@ me in replies or I'll lose your thread!!!

Thanks

[Anonymous]

Thank you very much for the response .it works