12 Month Avg in combo Chart

[Krish]

I am creating a combo chart which will have  revenue across 12 months as bar graph and total rev/12 months as Line graph.

the Revenue is rolling 12 month number.

rev =  sum({< MonthYear ={ "$(='>=' & Date(AddMonths(Max(MonthYear), -11), 'MMM-YY') & '<=' & Date(Max(MonthYear), 'MMM-YY'))"},Year,Month>}Revenue)

The Line graph needs to be a single straight line (for eg: if rev = 12000, and yearmonth = dec 2019, then line graph sould be a straight line of 1000 for all months ). but i am not able to achieve this.   how can this be achieved.

[sunny_talwar · Accepted solution]

You are having trouble with the line expression? May be this

Sum(TOTAL {< MonthYear ={ "$(='>=' & Date(AddMonths(Max(MonthYear), -11), 'MMM-YY') & '<=' & Date(Max(MonthYear), 'MMM-YY'))"}, Year, Month>} Revenue)/Count(DISTINCT Month)

[Krish]

It works!! Thanks!!

[Krish]

This works fine when only year is selected, but not when Month is selected.

If 2019 is selected, this works. But when Oct is selected, the line graph should find the avg. pf oct 2019 till nov 2018. 

[sunny_talwar · Accepted solution]

Try this

Sum(TOTAL {<MonthYear ={ "$(='>=' & Date(AddMonths(Max(MonthYear), -11), 'MMM-YY') & '<=' & Date(Max(MonthYear), 'MMM-YY'))"}, Year, Month>} Revenue)/Count(DISTINCT {<MonthYear ={ "$(='>=' & Date(AddMonths(Max(MonthYear), -11), 'MMM-YY') & '<=' & Date(Max(MonthYear), 'MMM-YY'))"}, Year, Month>} Month)