DAX function to count values not duplicated

[marquesj]

Hi all,

I am running a report for a wharehouse to check what is inside, what was removed and what has entered. 

I want to focus on what was removed from one day to another and exclude what entered. Below there's the structure of the table:

Date  //  Package  // Dwell

03/05/2024	1	2
03/05/2024	2	2
03/05/2024	3	2
03/05/2024	4	5
03/05/2024	5	5
03/06/2024	1	3
03/06/2024	2	3
03/06/2024	3	3
03/06/2024	6	0
03/06/2024	7	0

On March 5th there were 5 packages on the wharehouse and on March 6th there were also 5 packages, but packages 4 and 5 were removed and packages 6 and 7 have entered. The final result I want to find is 3 (number of packages that remained from one day to another removing the packages with Dwell=0 because they're new)

Thank you!

[Anonymous]

Hi @marquesj ,

1. The expression of column 2 can be modified as:

Column 2 =
IF('Table'[Column] = BLANK(),1,BLANK())

2. Create a calculated column to count new values.

Column 3 =
VAR _cou = CALCULATE(SUM('Table'[Column 2]),FILTER(ALL('Table'), 'Table'[Column 2] <> BLANK()&&'Table'[Date Dwell] = EARLIER('Table '[Date Dwell]) ))
RETURN
IF('Table'[Column 2] <> BLANK(),_cou,BLANK())

3. The final result is shown in the figure below. All data are displayed on March 5 because there is no previous date for comparison, so all data are considered new.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[marquesj]

HI @Anonymous 

It seems that isn't working. Dwell days are not contemplated and it's counting only the unique packages of day 6/03. If I manually put the filter on the visual for "Dwell = 0" I'm left with very few results which don't match the reality

[Anonymous]

Hi @marquesj ,

1. Create a calculated column to get the data retained from the previous day.

Column =
VAR _pre = CALCULATE(COUNT('Table'[Package]),FILTER(ALL('Table'),'Table'[Date Dwell] = EARLIER('Table'[Date Dwell]) - 1 && 'Table'[Package ] = EARLIER('Table'[Package ])))
RETURN
_pre

2. Create a calculated column to get the count of retained packages from the previous day.

Column 2 =
VAR _cou = CALCULATE(SUM('Table'[Column]),FILTER(ALL('Table'), 'Table'[Column] <> BLANK() &&'Table'[Date Dwell] = EARLIER('Table'[ Date Dwell])))
RETURN
IF('Table'[Column] <> BLANK(),_cou,BLANK())

3. If you want to view the retained data, you can filter out the data whose calculated column is not empty in the filters pane, as shown in the figure below.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[marquesj]

Hi Clara,

Thank you so much for helping out. Reading again my topic, I have made some confusion. The result I'm looking for is 2, as packages 4 and 5 were removed and therefore don't duplicate from one day to another.

Using this way I'll be given the opposite result that I need. The result I'm looking for is 2 as packages 4 and 5 were the ones that were removed: 

Thank you

[lbendlin]

COUNTROWS()-COUNTROWS(EXCEPT())

[lbendlin]

COUNTROWS(INTERSECT())