5 Replies Latest reply on Oct 18, 2016 1:09 PM by nataraj.vijayanagaram

# Pick Data from Prev Month if Data for current month is Blank

Hello,

I have a Table which has actuals that gets refreshed automatically.

MonthValue
January23
February24
March20
April22
May25
June23
July24
August25
September26
October
November
December

Is it possible to create a calculated field which will fill the empty (NULL) values for all the months to the latest month Data (Example - Fill Oct, Nov & Dec with 26). I have another table with the month where I have pipeline as 1,2 & 2 for Oct, Nov & Dec. If the calculated filed can take the prev month data I can then add with the second table to show a trend. Please advise.

Nataraj

• ###### 1. Re: Pick Data from Prev Month if Data for current month is Blank

Hi Nataraj,

Is your Month field is in actual date format??

• ###### 2. Re: Pick Data from Prev Month if Data for current month is Blank

Hi Praveen,

Yes the Month Field is in Actual Date format.

Thank you!

Nataraj

• ###### 3. Re: Pick Data from Prev Month if Data for current month is Blank

Hi ,

Tried Something , it will fill your requirement , but not most optimized solution.

Create a calculation like bleow.

IIF(ISNULL(SUM([Value])),LOOKUP(SUM([Value]),-1),SUM([Value]))

lets name this calculation as cal1

Use this calculation as your measure. The restriction with this method is if your having having only 1 continuous Null value it works fine. If you are having max 2 continuous Null value you need to create 2 calculations like wise if you have max 3 continuous Null values you have to create 3 calculations. as you are having only 12 values in your requirement you can create 11 calculations and use last calculation as measure.

Cal 2 will be like this :

IIF(ISNULL(SUM([Value])),LOOKUP(SUM([Cal1]),-1),SUM([Value]))

Only change with the previous calculation is we will change the filed as previous calculation (Highlighted in Bold)

so Cal 3 will be like this

IIF(ISNULL(SUM([Value])),LOOKUP(SUM([Cal2]),-1),SUM([Value]))

like this you can do.

If we could have Circular Reference allowed we could have done this with only one calculation.

Hope someone else will provide better solution.

• ###### 4. Re: Pick Data from Prev Month if Data for current month is Blank

Try the below formula

if isnull(LOOKUP(SUM ( [Value] ),0 )) then PREVIOUS_VALUE( SUM( [Value] ) ) else SUM([Value])  END

Thanks,

Praveen

2 of 2 people found this helpful
• ###### 5. Re: Pick Data from Prev Month if Data for current month is Blank

worked perfectly.

Thank you praveen