Find and count similar rows within next ten days

[Theiren]

Ok, I was not able to find a solution to my problem so let's try this way. I have actually two questions:

1. I have a dataset, where I have a few columns: ID, Company, Date and Amount. What I want to do is to compare the amount of the current row to the other rows and see, if the same amount (+/- 20%) within the next ten days within the same company can be found.

What I have done so far is this DAX - New Column:

CountRows = 
CALCULATE(COUNTROWS(Sheet1),
FILTER(Sheet1, EARLIER(Sheet1[Amount]) = Sheet1[Amount] || (EARLIER(Sheet1[Amount]) <= Sheet1[Amount] * 1.2 && EARLIER(Sheet1[Amount]) > Sheet1[Amount] * 0.8)),
FILTER(Sheet1, EARLIER(Sheet1[Company]) = Sheet1[Company]),
FILTER(Sheet1, EARLIER(Sheet1[Date]) < DATEADD(Sheet1[Date], 10, DAY)))

I have 20 rows (20 days) in my test dataset, and this DAX works fine the first 9 days, after that I don't get the correct result (maybe it can't find the "DATEADD" -part from the dataset?)

Here is my result:

So my first question is, how do I get the result until the end of the list? Of course the result for the last row can't be anything else but one.

2. My second question is, how can I take a closer look on those with CountRows value more than 1? I know that I can create another table and use CountRows as a filter, but if I get a result of 3, how can I drill down to see only those three rows? Should I create some kind of a new ID number which would "put together" my similar rows? How can I do that or maybe there is a better idea? 

I am very thankful for any help!

[v-yulgu-msft]

Hi @Theiren ,

Question1.

CountRows = 
CALCULATE(COUNTROWS(Sheet1),
FILTER(Sheet1, EARLIER(Sheet1[Amount]) = Sheet1[Amount] || (EARLIER(Sheet1[Amount]) <= Sheet1[Amount] * 1.2 && EARLIER(Sheet1[Amount]) > Sheet1[Amount] * 0.8)),
FILTER(Sheet1, EARLIER(Sheet1[Company]) = Sheet1[Company]),
FILTER(Sheet1, EARLIER(Sheet1[Date].[Date]) < DATEADD(Sheet1[Date].[Date], 10, DAY)))

Question2.

You can add an extra column to group records based on the number of [CountRows]. Then, add this [group] column into a slicer.

Group = CALCULATE(COUNT(Sheet1[CountRows]),ALLEXCEPT(Sheet1,Sheet1[CountRows]))

Best regards,

Yuliana Gu

[Theiren]

I did some testings with dates, and if I put it this way:

CountRowsTest = 

VAR PlusTen = DATEADD(Sheet1[Date].[Date];10;DAY)
VAR MaxDate = MAX(Sheet1[Date])
VAR LatestDate = IF(PlusTen>MaxDate; MaxDate; PlusTen)

RETURN
LatestDate

I get the correct dates:



However, if I put this together with my original CountRows, it won't work. I also tried to change the last filter to [Date] instead of EARLIER[Date], and I was already sure I nailed it. But no. The last five rows are now taking into account the first five rows. Whyyyyy?

CountRowsTest = 

VAR PlusTen = DATEADD(Sheet1[Date].[Date];10;DAY)
VAR MaxDate = MAX(Sheet1[Date])
VAR LatestDate = IF(PlusTen>MaxDate; MaxDate; PlusTen)

RETURN
CALCULATE(COUNTROWS(Sheet1);
FILTER(Sheet1; EARLIER(Sheet1[Amount]) = Sheet1[Amount] || (EARLIER(Sheet1[Amount]) <= Sheet1[Amount] * 1,2 && EARLIER(Sheet1[Amount]) > Sheet1[Amount] * 0,8));
FILTER(Sheet1; EARLIER(Sheet1[Company]) = Sheet1[Company]);
FILTER(Sheet1; Sheet1[Date] <= LatestDate))

 


/Theiren

[Theiren]

Yep, of course if the date is smaller than "LatestDate" it counts the first rows as well. I modified my DAX to:

CountRowsTest = 

VAR PlusTen = DATEADD(Sheet1[Date].[Date];10;DAY)
VAR MaxDate = MAX(Sheet1[Date])
VAR LatestDate = IF(PlusTen>MaxDate; MaxDate; PlusTen)

RETURN
CALCULATE(COUNTROWS(Sheet1);
FILTER(Sheet1; EARLIER(Sheet1[Amount]) = Sheet1[Amount] || (EARLIER(Sheet1[Amount]) <= Sheet1[Amount] * 1,2 && EARLIER(Sheet1[Amount]) > Sheet1[Amount] * 0,8));
FILTER(Sheet1; EARLIER(Sheet1[Company]) = Sheet1[Company]);
FILTER(Sheet1; Sheet1[Date] <= LatestDate && Sheet1[Date] >= LatestDate-10))

This is better, but not perfect. Now it compares only to the latter rows which is of course correct in principle, but now I am not able to see the "result rows". And within the last ten rows the date is always between Feb 10 and Feb 20. So I still have the problem how to pick the cases.



/Theiren

[Theiren · Accepted solution]

I forgot to reply myself once found the solution. But unfortunately the solution is too heavy with large datasets, so I am not able to use this with my real data. Anyway, here is the DAX:

CountRows = 

VAR PlusTen = DATEADD(Sheet1[Date].[Date],10,DAY)
VAR MinusTen = DATEADD(Sheet1[Date].[Date],-10,DAY)
VAR MaxDate = MAX(Sheet1[Date])
VAR MinDate = MIN(Sheet1[Date])
VAR LatestDate = IF(PlusTen > MaxDate, MaxDate, PlusTen)
VAR EarliestDate = IF(MinusTen < MinDate, MinDate, MinusTen)

RETURN
CALCULATE(COUNTROWS(Sheet1),
FILTER(Sheet1, EARLIER(Sheet1[Amount]) = Sheet1[Amount] || (EARLIER(Sheet1[Amount]) <= Sheet1[Amount] * 1.2 && EARLIER(Sheet1[Amount]) > Sheet1[Amount] * 0.8)),
FILTER(Sheet1, EARLIER(Sheet1[Company]) = Sheet1[Company]),
FILTER(Sheet1, Sheet1[Date] <= LatestDate && Sheet1[Date] >= EarliestDate))

And here is the result:

Unfortunately I didn't find any solution to my problem number 2. I might open a new thread about that.

/Theiren

[Theiren]

Hi Yuliana and thank you for your reply!

Question number 1:

Adding [Date] makes it count the rows until the end, but unfortunately it doesn't count the rows correctly. If I take the first company CO1, the first three rows should have value 3 (1st-3rd Feb), next similar row for CO1 is 16th Feb, which is not within the "next ten days". So I believe it now counts all the similar values within one company, but somehow it counts only "within the future days", since Feb 16th shows the correct value (one).

Question number 2:

I am not sure how this works? Should all similar CountRows numbers have one group? I.e. if CountRows is 4, the group value is 3, if CountRows is 3, the group value is 10? I believe not because that is not what happens. E.g. ID 4 has CountRows value 2 and it also gives group value 3? I also doubt this works with my real dataset (millions of rows). I believe in my real dataset the most of CountRows will be one, and a few will get the value of 2 or 3, but it is very unlikely to have more values.

I am very glad of your help, thank you!

/Theiren

@v-yulgu-msft