TO find Earlier date but not prior to 7 days earlier

[sharonmathew]

Hi all,

i have one table with few filters,

but i cant' write  calculate column to find duplicates only if its within previous week

http://powerpivotforum.com.au/viewtopic.php?f=6&t=1060

[v-ljerr-msft]

Hi @sharonmathew,

Kindly help me create a measure to yield an index for a small table to get initial entry as -1 and then the date differences if the earlier date is within one week from that date.

 Dax Earlier.xlsx

---

Could you be more precisely with the logic to get the initial entry? 

From your shared sample above, 8 is greater than 1, why it is initial entry(-1)? 

Regards

[sharonmathew]

it calculates the date differences.

but the challenge i am facing is. it is a series with a lot of 

duplicated dates which gives me 0 as its difference from the previous entry.

there are dates which would fall within a bracket of 7 days. where it shows the difference of 7 or less.

and dates which are otherwise. which are not of concern.

so. in order to have them filtered at the same time. i used a logic.

as -100 as those which we exempt.

-1 is the start or the intitial entry for its upcoming partner where it will show either a 0 or datediff less than 7.

so the results column would look like

but note there are few fields which need to match with its previous ones.

-100

-100

-100

-100

-100

-2

7

4

3

5

-2

2

3

4

-1

0

3

-2

3

4

-1

3

and so on and so forth.

- 1 is the initial entry for duplicated upcoming dates.

-2 is the initial entry for those date differences are not greater than 7

-100 or blank().

thanks for joining my discussion. its a pretty difficult one. i could work my head around and got the results done. but i need a single DAX code. as my current system is. with a lot of calculatd columns. 

i can send you a working file, if you would like to take a look.

[sharonmathew]

https://social.msdn.microsoft.com/Forums/en-US/449ee622-99f7-4321-93fa-a9e6649f0549/if-4-fields-earl...

i had worked it with a series of calculated fields. but i want to combine it in one program.

dpx is duplicates, edpx is excat duplicates. the diff is edpx is inclusive of Date field. the dpx doesnt consider date column.

rank is for dpx ranking. edpx ranking is for exact duplicate ranking.

previous day is the previous occurrence, prev ref is its index number.

index is index number in other words just 123 row reference number just to keep track.

crnt day works in the opposite direction to track the first value of set of prev day. only for the sake of identifying the initial. so we used min instead of max. and reversed the < arrows opposite to prev day pattern.

so, the area where i am struggling is, how to identify the initial row records of Duplicates. where there is a series of records. 

in between the duplicates there will be excat duplicates [including date column]. where it should start -1 ,0 pattern. where as the other should start with -2 , n 

and the rest should go with -100 or any number which can be assigned from negative value.

the reason we assign like this is to sort them accordingly. and also to filter between a value and another value. say between -1 and 0, or say between -2 and 7. and so on.