Graph to extend formula to all months

[anagharao]

Hi Everyone

I have data in the format :

Account	Department	Vendor	Date	Actual
Acc1	Dept1	Vend1	Jan-2019	1200
Acc1	Dept2	Vend1	Jan-2019	1748
Acc2	Dep2	Vend2	Feb-2019	122
...	...	...	...	...

I would like to get a graph with the below measures and Date [MMM-YY] as the dimension

• Cost (Jan, Feb...) : Actuals that come in
• Avg Annld Cost : for the rest of the months in the year
  (Sum of all months available) + (Latest month Cost* (12 - number of months passed))
  Example: IF the latest month is Feb
  (Jan + Feb) + (Feb *(12 - 2))

  Any suggestions for the second formula?

[lironbaram]

hi 

you'll need to load the months into your date field and then it's wont be that complicated 

you'll expression will look like this : 

if(Sum(Actual)=0,
(sum(total Actual)+sum({<Date={"$(=max(Date))"}>}Actual)*(12-num(month(max(Date)))))/12,Sum(Actual))

[anagharao]

Thank you for the reply!

I did try TOTAL and Set analysis to fetch the latest date. There are two issues with that:

- Since the chart is dimensioned by Month-YY the set analysis for the current month cant be extended to all future months

- The total doesnt work when i make selections in any dimensions