Incorrect results returned in DAX measure used in Matrix Table

[m_roussakis]

I have a matrix table where I display a distinct count of items in the last 7 days. I use the relative date filter, and this has worked perfectly. 

Recently I've been asked to add a comparison column on this table where I compare the last 7 days with the 7 day period previous to that. In order to do this, I have to create a measure for both time periods (can no longer use the relative filter).

My issue is when I compare the side by side of the last 7 day period for the relative date filter vs. my DAX measure, the numbers do not match.

I need to display all items which show a 0% rate in the last 7 days. 

Example:

Here's the table using the relative date filter, which displays the CORRECT distinct count of 154:

Yet if I try to add my DAX calculation as the Value in the table, and apply the same 0% filter, I get a different result of 81 

Here is the DAX measure I wrote to get the distinct count for last 7 days. Pergaps there's a problem with it?

Last 7 Days = calculate(DISTINCTCOUNT(Alpha[Item]), ('Alpha'[Date] >= TODAY()-7 && 'Alpha'[Date] < TODAY()))

Thanks.

[Rudy_D]

Hey,

How do you count items in your relative date filter solution ? Are you sure that it is a DISTINCT count as it is in your solution based on a measure ?

Regards

Rudy

[m_roussakis]

In my relative filter count, it is based off this value:

Total Items= DISTINCTCOUNT(Alpha[Item])

So yes I used a distinct count in there as well. 

I've narrowed this issue down to the 0% filter. If I display the tables side by side, one using the relative filter, and the other using the calculated measure, and I do not apply 0% filter, the totals match. It's only when I apply the 0% that it goes sideways for my calculated measure, and we have a mismatch. 

[Rudy_D]

Can you try :

Last 7 Days = calculate(DISTINCTCOUNT(Alpha[Item]), FILTER('Alpha', 'Alpha'[Date] >= TODAY()-7 && 'Alpha'[Date] < TODAY()))

I tried both solutions in a sample of data and it works.
If it's not ok for you, I think we'll need a sample of your work because there's maybe something hidden like a filter

[m_roussakis]

Same result as my DAX.

The data is very complex. I have so many calculations going on, and its sensitive, so I can't share anything unfortunately. 

Is there a way I can create a new 2 column table which has 'Item' and '%', and then use that to filter the table? Not sure if this would even work....

[m_roussakis]

I'm really stumped on this one. Would anyone have any idea how I can get this to calulate the CORRECT amount of 0% items within the last 7 day period?

[bolfri]

Yes, can you provide some sample data within last 16 days? You can use columns such as product A B C D E etc, but holding your column names. 

[bolfri]

I've created my own set of data to try this out and it works ok for me.

Maybe the problem is with TIMEZONE in your case? Where do you live? What is the difference between your localtime and UTC?

[m_roussakis]

I am in Eastern Time. I dont think thats the reason though. 

[bolfri]

It might be. TODAY() returns a current date for the UTC, while Relating filtering is based on your current timezone.

Replace TODAY() with DATE(2023,1,20) and check if it solves your problem. If yes then we know what is the problem. If it still return wrong results, then a sample data is needed.