Forum Discussion

acg's avatar
acg
Icon for Resolver I rankResolver I
4 years ago
Solved

Current versus last year on KPI visual

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's avatar
    acg
    4 years ago

    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
    )

5 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi acg 

    you can use

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

    • acg's avatar
      acg
      Icon for Resolver I rankResolver I
      4 years ago

      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. 
  • acg's avatar
    acg
    Icon for Resolver I rankResolver I
    4 years ago

    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?

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

     

     

     

  • acg's avatar
    acg
    Icon for Resolver I rankResolver I
    4 years ago

    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's avatar
      acg
      Icon for Resolver I rankResolver I
      4 years ago

      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
      )