Distinct Count with Filter- Year Minus 1,2

[Dailem]

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 selected (I have a Calendar in my model, but then a reporting calendar which only shows quarter-end dates).  So my report is filtered to 9/30/2023, for example:

Measure1 =

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

...however, I am trying to look at this same measure one year ago, two years ago, etc..  I have another measure that looks at prior year sales by filtering using the following in a calculate function:

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

...I have tried to add a second filter, but I cannot seem to get it to work properly.  When it does work, it only returns the existing date distinct counts- not those from the prior year.  How can I add a date filter on top of my existing Measure1 above to return the prior year account counts?  Any help is greatly appreciated!!

[Dailem · Accepted solution]

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!!

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but please try creating the semantic model's relationship between Calendar Table and Fact Table by using Calendar[Date] column, not Calendar[QuarterEndDate] column.

[Anonymous]

Hi @Dailem ,

Can you provide some example data that can be used to more clearly articulate the problem you are experiencing.  Please provide more details with your desired out put and pbix file without privacy information.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.