Count of the past five years

[Anonymous]

Hello!

First, I have a calendar table.

I would like something like the below, but that I can then filter by date (if I filter by January 2020, it will give me the count for January, and the sum of all the Januaries of the past 5 years, including this one). Then potentially I can do a 5 year average for that date period.

This Year	Past five years

Thanks!

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar:

Calendar = CALENDARAUTO()

 

You may create measures as below.

Sum = 
var y = SELECTEDVALUE('Calendar'[Year])
var m = SELECTEDVALUE('Calendar'[Month])
return
IF(
    HASONEVALUE('Calendar'[YearMonth]),
    CALCULATE(
        SUM('Table'[Value]),
        FILTER(
            ALL('Table'),
            YEAR([Date])>=y-5&&
            YEAR([Date])<y&&
            MONTH([Date])=m
        )
    )
)

Count = 
var y = SELECTEDVALUE('Calendar'[Year])
var m = SELECTEDVALUE('Calendar'[Month])
return
IF(
    HASONEVALUE('Calendar'[YearMonth]),
    COUNTROWS(
        FILTER(
            ALL('Table'),
            YEAR([Date])>=y-5&&
            YEAR([Date])<y&&
            MONTH([Date])=m
        )
    )
)

Avg = 
var y = SELECTEDVALUE('Calendar'[Year])
var m = SELECTEDVALUE('Calendar'[Month])
return
IF(
    HASONEVALUE('Calendar'[YearMonth]),
    CALCULATE(
        AVERAGE('Table'[Value]),
        FILTER(
            ALL('Table'),
            YEAR([Date])>=y-5&&
            YEAR([Date])<y&&
            MONTH([Date])=m
        )
    )
)

Result:

Best Regards

Allan

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

[amitchandak]

@Anonymous , One of the way with Date table and time intelligence is

last 5 MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))+ CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
+ CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-24,MONTH))) + CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-36,MONTH)))
+ CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-48,MONTH)))+ CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-60,MONTH)))