Compare a column with itself in a context modified by filters

[Anonymous]

Hello,

I have a table visual showing hours spent on some projects, the first column is the year of the date field of my table.

And the last column is a measure that must return the historical average of time spent yearly from the beginning to the previous year.

I managed to calculate it for 2021 using 'Table'[date].[Year]<YEAR(TODAY()) in the filter section of the calculate function but if I scroll down the table visual to 2020 it's not working anymore because it takes 2020 into account instead of stopping at 2019.

So I wish to change the filter to something like 'Table'[date].[Year]<FILTERED('Table'[date].[Year]) but I don't find the good formula.

How would you proceed to compare the unfiltered date value and the filtered one ? whithout using a calendar table 

Thanks in advance.

Regards,

Cado

[v-kkf-msft · Accepted solution]

Hi @Anonymous ，

Try the following formula:

Measure = 
CALCULATE(
    AVERAGE('Table'[Value]),
    FILTER(
        ALL('Table'[Year]),
        'Table'[Year] < MAX('Table'[Year])
    )
)

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[Anonymous]

Hi @v-kkf-msft 

Thank you for your help, the filter with the MAX function works perfectly !

Have a nice day.

Cado