Current versus last year on KPI visual

[acg]

I have data over several years, say from 2017 to 2021.

For some of the KPI, I worked out the counts of a variable for latest / max year (2021) versus the count of the earliest / min year where we have data from (2017). 

Count of Audits conducted Max =

VAR MAXSD = CALCULATE(MAX('2017-2021'[Year]),ALLSELECTED('2017-2021'))

VAR MAXYSD = CALCULATE(MAX('2017-2021'[Year]),'2017-2021'[Year] = MAXSD)

RETURN

CALCULATE

(

COUNT('2017-2021'[Conducted]),

'2017-2020'[Conducted?] = "Y",

'2017-2021'[Year] = MAXYSD

)

and for the earliest counts the min:

Count of Audits conducted Min =

VAR MINSD = CALCULATE(MIN('2017-2021'[Year]),ALLSELECTED('2017-2021'))

VAR MINYSD = CALCULATE(MIN('2017-2021'[Year]),'2017-2021'[Year] = MINSD)

RETURN

CALCULATE

(

COUNT('2017-2021'[Conducted]),

'2017-2020'[Conducted?] = "Y",

'2017-2021'[Year] = MINYSD

)

-- both of these calculations work fine and as expected. 

--

What I want to do now is to have KPI with the counts of a variable for latest / max year (2021) and the previous year, 2020.

I thought I could just leave the MAX calculation as is, and change the MIN like this:

VAR MINSD = CALCULATE(MAX('2017-2021'[Year] -1),ALLSELECTED('2017-2021'))

VAR MINYSD = CALCULATE(MAX('2017-2021'[Year] -1),'2017-2021'[Year] = MINSD)

This does however not work. I think it is partly because the Max / Min functions only accepts a column reference as an argument.

So how can I get around this?

Is there a better way to solve this altogether? 

[acg]

So I have found now a solution, which I show below. 

However, for whatever reason it is tremendouly memory hungy to the point that it is not sustainable. 

Has anyone a better solution??

This works, but as said: at a cost 

Count of Audits conducted Min =

VAR MINSD = CALCULATE((MIN('2017-2021'[Year])-1),ALLSELECTED('2017-2021'))

VAR MINYSD = CALCULATE((MIN('2017-2021'[Year])-1),'2017-2021'[Year] = MINSD)

RETURN

CALCULATE

(

COUNT('2017-2021'[Conducted]),

'2017-2020'[Conducted?] = "Y",

(('2017-2021'[Year])-1) = MINYSD

)

[acg · Accepted solution]

Ok Solution found:

Count of Audits conducted Min =

VAR MINSD = CALCULATE((MIN('2017-2021'[Year])-1),ALLSELECTED('2017-2021'))

VAR MINYSD = CALCULATE((MIN('2017-2021'[Year])-1),'2017-2021'[Year] = MINSD)

RETURN

CALCULATE

(

COUNT('2017-2021'[Conducted]),

'2017-2020'[Conducted?] = "Y",

'2017-2021'[Year]= MINYSD

)

[acg]

Hi @tamej1 et all, the solution provided seems not to work. 

I give an example and make it slightly easier:

The Max I am using for te KPI measure is:

Count of Audits conducted Max =

VAR MAXSD = CALCULATE(MAX('2017-2021'[Year]),ALLSELECTED('2017-2021'))

VAR MAXYSD = CALCULATE(MAX('2017-2021'[Year]),'2017-2021'[Year] = MAXSD)

RETURN

CALCULATE

(

DISTINCTCOUNT('2017-2021'[Entity]),

'2017-2020'[Conducted?] = "Y",

'2017-2021'[Year] = MAXYSD

)

--------------

And for the Min / Last or previous year I used the solution suggested

Count of Audit Firms Last Year =

VAR MINYSD = MINX(

TOPN ( 2, ALLSELECTED ('2017-2020'),'2017-2020'[Year] ),

'2017-2020'[Year])

RETURN

CALCULATE

(

DISTINCTCOUNT('2017-2020'[Entity]) = MINYSD

)

I am getting this table output:

Below is the data I used. 

How can I make this working?

Year	Entity
2017	Firm1
2017	Firm2
2017	Firm3
2017	Firm4
2017	Firm5
2017	Firm6
2017	Firm7
2017	Firm8
2017	Firm9
2017	Firm10
2017	Firm11
2017	Firm12
2017	Firm13
2017	Firm14
2017	Firm15
2017	Firm16
2017	Firm17
2017	Firm18
2017	Firm19
2017	Firm20
2017	Firm21
2017	Firm22
2017	Firm23
2017	Firm24
2017	Firm25
2017	Firm26
2017	Firm27
2018	Firm28
2018	Firm29
2018	Firm30
2018	Firm31
2018	Firm32
2018	Firm33
2018	Firm34
2018	Firm35
2018	Firm36
2018	Firm37
2018	Firm38
2018	Firm39
2018	Firm40
2018	Firm41
2018	Firm42
2018	Firm43
2018	Firm44
2018	Firm45
2018	Firm46
2018	Firm47
2018	Firm48
2018	Firm49
2018	Firm50
2018	Firm51
2019	Firm52
2019	Firm53
2019	Firm54
2019	Firm55
2019	Firm56
2019	Firm57
2019	Firm58
2019	Firm59
2019	Firm60
2019	Firm61
2019	Firm62
2019	Firm63
2019	Firm64
2019	Firm65
2019	Firm66
2019	Firm67
2019	Firm68
2019	Firm69
2019	Firm70
2019	Firm71
2019	Firm72
2019	Firm73
2019	Firm74
2019	Firm75
2019	Firm76
2019	Firm77
2019	Firm78
2020	Firm79
2020	Firm80
2020	Firm81
2020	Firm82
2020	Firm83
2020	Firm84
2020	Firm85
2020	Firm86
2020	Firm87
2020	Firm88
2020	Firm89
2020	Firm90
2020	Firm91
2020	Firm92
2020	Firm93
2020	Firm94
2020	Firm95
2020	Firm96
2020	Firm97
2020	Firm98
2021	Firm99
2021	Firm100
2021	Firm101
2021	Firm102
2021	Firm103
2021	Firm104
2021	Firm105
2021	Firm106
2021	Firm107
2021	Firm108
2021	Firm109
2021	Firm110
2021	Firm111
2021	Firm112
2021	Firm113
2021	Firm114
2021	Firm115
2021	Firm116
2021	Firm117
2021	Firm118
2021	Firm119

[tamerj1]

Hi @acg 

you can use

VAR MINYSD =
MINX (
TOPN ( 2, ALLSELECTED ( '2017-2021' ), '2017-2021'[Year] ),
'2017-2021'[Year]
)

[acg]

@tamerj1 

as mentioned above, I tried the follwing:

Count of Audit Firms Last Year =

VAR MINYSD = MINX(

TOPN ( 2, ALLSELECTED ('2017-2020'),'2017-2020'[Year] ),

'2017-2020'[Year])

RETURN

CALCULATE

(

DISTINCTCOUNT('2017-2020'[Entity]) = MINYSD

)

I don't understand why it does not work, but I printed the output above. Just wanted to add that 'year' is a 'whole number. I tried to convert it to yyyy, but the output it gave me was 1905 throughout. So I reverted it back to whole number. Maybe that is part of whyt it does not work that what. I thought your solution was as such neat, as you just say, take the second year.