Previous Value from slicer selection

[Anonymous]

Hi, 

Wondering if anyone can help me on this one:

I have the following simple measure: 

Total = COUNTROWS('Fleet (Cars)')

In another table we have the labels for year ranges such as:

Range	Index
2017-2018	1
2018-2019	2

When the user clicks the slicer, it shows the number of rows for that period, but I could also like to automatically show the previous period. I have tried with things like the following but to no avail.

Previous = CALCULATE(COUNTROWS('Fleet (Cars)');'Ranges'[Index]-1)

Any suggestions how I could achieve this?

Many thanks, 

Matt

[Zubair_Muhammad]

Hi @Anonymous

Give this a shot

Previous =
CALCULATE (
    COUNTROWS ( 'Fleet (Cars)' ),
    FILTER (
        ALL ( 'Ranges'[Index] ),
        'Ranges'[Index]
            = SELECTEDVALUE ( 'Ranges'[Index] ) - 1
    )
)

[Anonymous]

Thanks @Zubair_Muhammad but getting a multiple column error on this one:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[Zubair_Muhammad]

Hi @Anonymous

Could you show me a screen shot? or share the file?

How are the tables related?

[Anonymous]

Relation is a bidireccional one to many between the IDs of the ranges:

@Zubair_Muhammad Thanks for your assistance!

Matt

[Anonymous · Accepted solution]

Hi again, 

Just to let you know we've managed to figure it out with the following:

var a = CALCULATE(Max('Ranges'[Index])-1)
var b = CALCULATE(
   DISTINCTCOUNT('Fleet (Cars)'[id]);
    filter(
      ALL('Fleet (Cars)');true());'Ranges'[Index]=a;)
return b 

Regards, 

Matt