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 ha...
  • 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
    )
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
)