Compare date values from the latest report against previous report

[kimloh_p]

Hi Community,

Appreciate some help as im having trouble thinking what DAX function to use.

I have a single table that contains key fields like [report date], [Unique_ID]. The trouble i'm having is that the report date is not consistent and I would like to add [Removed] and [New] calculated columns with the results below and as per logic:

  if Unique_ID is not found in previous report, mark product as New.

  if Unique_ID is not found in next report, mark product as Removed.

Date (mm/dd/yyyy)	Unique_ID	New	Removed
01/13/2020	{12f31-432fd-3r5i9}
01/13/2020	{4rtk-rr42d-3prr6}	Y
01/05/2020	{543mk-g42fd-3r89p}		Y
01/05/2020	{eer3-ttedo-3688}		Y
12/13/2019	{eer3-ttedo-3688}
12/13/2019	{543mk-g42fd-3r89p}
12/13/2019	{uutu3-oo5do-4678}	Y	Y
12/13/2019	{12f31-432fd-3r5i9}	Y
12/13/2019	{xxau3-wwepq-44548}	Y	Y
12/13/2019	{bbru3-gwggq-43348}	Y	Y
11/20/2019	{543mk-g42fd-3r89p}
11/20/2019	{eer3-ttedo-3688}	Y
11/20/2019	{rryu3-eefffg-5gr48}		Y
10/31/2019	{543mk-g42fd-3r89p}	Y
10/31/2019	{rfdde-4353g-43748}		Y
10/31/2019	{aabbc-45678-43748}		Y

Note that when the Unique_ID first appear in the current report, it may continue to be in subsequent future reports until it gets removed and does not appear in the latest report. Therefore, I need to track which ID was new in the latest report and which was removed based on comparing against the previous report. Above is a snippet of the dataset, in reality there are many other reports in 2019.

[JarroVGIT]

Hi @kimloh_p ,

First off: big thank you for providing sample data!

Then on to your question. Interesting question but not as complicated as I initially thought. I assume everything on the same date is from the same report, you are not really clear on that in your question.

Also, based on your logic, there should be a bunch more Y's in your example data. By definition, the last date (first row) should have a 'Y' at remove because there are no entries of that ID after that row (it is afterall the last inserted row). If the previous sentence is correct, the Calculated Columns DAX you are looking for are these:

New = 
VAR _curID = Table2[Unique_ID]
VAR _curDate = Table2[Date]
VAR _counts = COUNTROWS(FILTER(Table2, Table2[Date] < _curDate && Table2[Unique_ID] = _curID))
RETURN
IF(_counts = 0, "Y")

Removed = 
VAR _curID = Table2[Unique_ID]
VAR _curDate = Table2[Date]
VAR _counts = COUNTROWS(FILTER(Table2, Table2[Date] > _curDate && Table2[Unique_ID] = _curID))
RETURN
IF(_counts = 0, "Y")

 This results in this table

Let me know if this works for you! 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[kimloh_p]

both solutions worked @JarroVGIT , thank you so much!

In additon i had wanted to create a Measure to calculate the total distinct Unique_ID group by Color category. And perhaps in the visuals i can create a slicer based on color. How should i go about writing the functions?

Date Unique_ID New Removed Color

01/13/2020	{4rtk-rr42d-3prr6}	Y		Orange
01/13/2020	{4rtk-rr42d-3prr6}	Y		Red
01/13/2020	{12f31-432fd-3r5i9}			Yellow
01/13/2020	{12f31-432fd-3r5i9}			Red
01/05/2020	{eer3-ttedo-3688}		Y	Red
01/05/2020	{eer3-ttedo-3688}		Y	Blue
01/05/2020	{543mk-g42fd-3r89p}		Y	Yellow
12/13/2019	{xxau3-wwepq-44548}	Y	Y	Orange
12/13/2019	{uutu3-oo5do-4678}	Y	Y	Red
12/13/2019	{eer3-ttedo-3688}			Blue
12/13/2019	{bbru3-gwggq-43348}	Y	Y	Green
12/13/2019	{543mk-g42fd-3r89p}			Yellow
12/13/2019	{12f31-432fd-3r5i9}	Y		Blue
12/13/2019	{12f31-432fd-3r5i9}	Y		Orange
11/20/2019	{eer3-ttedo-3688}			Blue
11/20/2019	{rryu3-eefffg-5gr48}		Y	Green
11/20/2019	{543mk-g42fd-3r89p}			Yellow
11/20/2019	{543mk-g42fd-3r89p}			Yellow
10/31/2019	{rfdde-4353g-43748}	Y	Y	Blue
10/31/2019	{rryu3-eefffg-5gr48}	Y		Green
10/31/2019	{eer3-ttedo-3688}			Blue
10/31/2019	{aabbc-45678-43748}		Y	Green
10/31/2019	{543mk-g42fd-3r89p}			Yellow
10/18/2019	{aabbc-45678-43748}			Green
10/18/2019	{eer3-ttedo-3688}	Y		Blue
10/18/2019	{543mk-g42fd-3r89p}			Blue
10/18/2019	{543mk-g42fd-3r89p}			Yellow
10/01/2019	{aabbc-45678-43748}			Blue
10/01/2019	{543mk-g42fd-3r89p}			Yellow
09/13/2019	{aabbc-45678-43748}	Y		Blue
09/13/2019	{543mk-g42fd-3r89p}	Y		Yellow

[kimloh_p]

@JarroVGIT , I managed to figure how to use distinctcount and filter using calculate function. So all good now. Reallly appreciated your help, thanks a bunch!

[kimloh_p]

HI @JarroVGIT 

Thank you for your prompt response, and saving my sleepless nights, really appreciate it. I wouldn't have worked it out on my own. 

The solution for [New] Column is perfect, but the solution of [Removed] needs a little more tweak... because if the Unique_ID is in the last report (13 Jan 2020), it should not be reflected in [Removed] column. How do I incorporate this exceptional condition?

[Anonymous]

Whoops, wrong account! See reply below:)

[JarroVGIT · Accepted solution]

Please ignore above comment, that is from other account!

Hi, something like this (typed on phone dus forgive my typos)
Removed =
VAR _curID = Table2[Unique_ID]
VAR _curDate = Table2[Date]
VAR _counts = COUNTROWS(FILTER(Table2, Table2[Date] > _curDate && Table2[Unique_ID] = _curID))
RETURN
IF( MAX(Table2(Date]) > _curDate, IF(_counts = 0, "Y"))
Let me know if this works!