Forum Discussion

Dailem's avatar
Dailem
Frequent Visitor
2 years ago
Solved

Distinct Count with Filter- Year Minus 1,2

I'm trying to get a distinct count of accounts that are filtered to only include accounts with Amount > 0.  The measure below gives me that count based upon the existing slicer quarter that I have se...
  • Dailem's avatar
    Dailem
    2 years ago

    I was able to resolve this on my own.  The way that I was able to get the result was to use the initial measure in a second measure.  So first measure is:

     

     

    Measure1 =

    CALCULATE(
    DISTINCTCOUNT(Table1[AccountKey]),
    FILTER(Table1, Table1[Amount] > 0))

     

    ...second measure is

    Measure2 = 

    CALCULATE(Measure1, dateadd('Calendar'[Date],-1,year))

     

    ...this returns the same distinct count of the accountkey column, but does so for the prior year.  Thanks for all who responded!!

Dailem's avatar
Dailem
Frequent Visitor
2 years ago

I was able to resolve this on my own.  The way that I was able to get the result was to use the initial measure in a second measure.  So first measure is:

 

 

Measure1 =

CALCULATE(
DISTINCTCOUNT(Table1[AccountKey]),
FILTER(Table1, Table1[Amount] > 0))

 

...second measure is

Measure2 = 

CALCULATE(Measure1, dateadd('Calendar'[Date],-1,year))

 

...this returns the same distinct count of the accountkey column, but does so for the prior year.  Thanks for all who responded!!