I have a requirement to get 5 year average for revenue.

[Anonymous]

I have a table <Sales> with fields revenue, invoice date, year, month. I have to map a line chart with X as month, Y as revenue and filter is current year . So i need to map 5 year average of revenue for each of the month in 2020. Can you please share a way to do that

[Ashish_Mathur]

Hi,

Share some data and show the expected result in a simple Table format.  Once we compute the figures correctly, we can build any chart we want.  It will be best, if you can share an MS Excel workbook with your formulas there so that your calculation logic can be easily understood.

[Icey · Accepted solution]

Hi @Anonymous ,

I think you want to show the five-year average of the month on the line chart. If my understanding is right, here’s my idea.

1. The source data is as below.

2. Create a calendar table.

Date =
VAR _calendar =
    CALENDAR ( MIN ( 'Sales'[invoice date] ), MAX ( 'Sales'[invoice date] ) )
RETURN
ADDCOLUMNS ( _calendar, "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ) )

3. Write a measure to calculate the average.

ave = 
VAR t =
    SELECTEDVALUE ( 'Date'[Year] )
RETURN
    CALCULATE (
        DIVIDE ( SUM ( Sales[revenue] ), 5 ),
        FILTER ( ALL ( 'Date' ), 'Date'[Year] <= t && 'Date'[Year] >= t - 4 )
    )

4. After the measure is created, you can create the line chart and open Single select in Format.

You can check more details from here.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , for this year vs last year with help from date table

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

for rolling

Rolling 60 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-60,MONTH))
Rolling 60 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-60,MONTH))

You can divide by 60 for Avg or use Average function in place of sum