topic Re: Replicate same value in all month where revenue is blank till we have new non blank revenue. in DAX Commands and Tips

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

Anonymous — Tue, 17 Oct 2023 14:20:43 GMT

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.

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

DallasBaba — Tue, 17 Oct 2023 22:37:20 GMT

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

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

Anonymous — Mon, 23 Oct 2023 09:11:59 GMT

Thank you very much for the response .it works