Return Filtered Year +1

[Anonymous]

Hello,

For a calculated field, how do i filter for the currently filtered year +1. The calculated field i'm creating is currently based on another calculated field, so i can't use the 'ALL' DAX functions on the count. Please see the example below:

Year Slicers Currently Filtered for 2020. I want my calculated field to filter for 2021.

Count for Next Year: CALCULATE([Count],ALLSELECTED('Raw Data'[year])='Raw Data'[year]+1 )

[v-shex-msft · Accepted solution]

Hi @Anonymous,

If you do not want to calculate based on filters, you need to break the current filter effect on your table first, then you can use variable extract and store selected value and add this into conditions to calculate with custom filter range.

Measure =
VAR selected =
    MAX ( 'Raw Data'[Year] )
RETURN
    CALCULATE (
        COUNT ( 'Raw Data'[Column] ),
        FILTER ( ALL ( 'Raw Data' ), [Year] = selected + 1 )
    )

Regards,

Xiaoxin Sheng

[VasTg]

@Anonymous 

Use a variable..

measure = var a = max('Raw Data'[year])+1
return CALCULATE([Count],ALLSELECTED('Raw Data'[year])=a )

If it helps, mark it as a solutin

Kudos are nice too

[Anonymous]

Not working, the calculate function says it cannot determine a single value since it's using the ALLSELECTED and isn't filtered for a single year value. The underlying data has a few years worth of data. 

[amitchandak]

Try like

last year =
var _thisyear = maxx(table,table[year])+1
return
calculate(count(table[sales]),all(table[year]),table[year]=_thisyear)

[Anonymous]

I don't know if this will work. I can't use the count function on the underlying data in the table. My count is based on a calculated field, which has a value that's derived from about 15 other calculated fields because the user wants to be able to see 4 KPIs as either a variance, count, or difference in one table (what's returned depends on user input from slicers). So the count is just one part of a switch function, where all the possible values are derived from about 60 different caalculated fields. 

I basically need a calculated field to return values that i'm explicitly unfiltered for in the raw data. The report is filtered for a particular year, but there is one visual that shows "Next Year", and the year of this visual depends on the current year selection.