Comaring data based on date

[mrvamsidhar]

Hello, I am having power bi data set which is having data on different dates. I want to add a "Status" cloumn to check if resource exists in previous date. If exists in previous date i need add "Existing", if not exists in current date "Deleted", If exists only in current date but not in previous date "New".

At the end I need to generate a report to identfy list of resources which are added/deleted/existing. Any inputs on how to do this?

Date	Resource	Amount
Mar-18	CLS1	10
Mar-18	CLS2	20
Mar-19	CLS1	10
Mar-19	CLS3	15
Mar-20	CLS1	12
Mar-20	CLS3	12
Mar-20	CLS4	12

Date	Resource	Amount	Status
Mar-18	CLS1	10	Existing
Mar-18	CLS2	20	Deleted
Mar-19	CLS1	10	Existing
Mar-19	CLS3	15	New
Mar-20	CLS1	12	Existing
Mar-20	CLS3	12	Existing
Mar-20	CLS4	12	New

[Phil_Seamark]

Hi @mrvamsidhar

This is a DAX approach which I think might be close but I don't fully understand your requirements.  Your definition says one thing but your test data doesn't match that.

Status Column = 
var ResourceColumn = 'test'[Resource]
var DateColumn = test[Date]
RETURN SWITCH(
    CALCULATE(
            COUNTROWS('test'),
            Filter('test',
                'test'[Resource]= ResourceColumn 
                && 'test'[Date] < DateColumn
                )
               ),blank(),"New","Existing")

[MarcelBeug]

First I would like to remark that it is a bit strange to have "Deleted" on the last day of the resource. I would expect an additional row with the next date for that resource and status "Deleted".

Now you have an issue with 1 day resources: are they "New" or "Deleted"? In my solution below it will be "New".

In my solution, a resource will not be marked "New" on the first date that appears in the file (i.c. March 18); likewise a resource will not be marked as "Deleted" on the last date that appears in the file.

The logic for determining the status is: check for "New" then check for "Deleted" and what remains is "Existing".

Now the logic of the query:

In general if you need data from different rows, it's most efficient to get all the data you need on 1 row.

You can watch the first minute of this video (I created for another question) to see how you get data from the previous row on the current row. I applied the same technique for you (and similarly to get data from the next row on the current row).

1. The file is imported in Power Query (i.c. from the current Excel workbook) and automatically typed.
   I adjusted type datetime to date.
2. I first added an Index as Original sort so the results will be sorted back to the original sort at the end.
3. Sort on resource and date.
4. Now you need data from the previous and the next row.
   In order to get the data on the same row, I added 2 indices, starting with 0 and 1 respectively.
   Next the table is merged with itself twice: first joined on the 0-Index and 1-Index to get the data from the previous row,
   then on the 1-index and 0-index to get the data from the next row.
5. After each merge code was created, I adjusted the code and changed "NewColumn" in "Previous" resp. "Next"
6. After the merges you have 2 columns with nested tables that need to be expanded.
   Only the Resource is required. With expansion, keep the "Use original column name as prefix" checked,
   so you get Previous.Resource and Next.Resource.
7. I added 2 lines in the advanced editor to determine the min and max date in the file.
8. I added a column with the Status according to the logic I outlined above.
9. As finishing touches I sorted back to the original sort and removed all helper columns.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Resource", type text}, {"Amount", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Original Sort", 1, 1),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Resource", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Index2" = Table.AddIndexColumn(#"Added Index1", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index2",{"Index"},#"Added Index2",{"Index.1"},"Previous",JoinKind.LeftOuter),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries",{"Index.1"},#"Merged Queries",{"Index"},"Next",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries1", "Previous", {"Resource"}, {"Previous.Resource"}),
    #"Expanded Next" = Table.ExpandTableColumn(#"Expanded Previous", "Next", {"Resource"}, {"Next.Resource"}),
    MinDate = List.Min(#"Sorted Rows"[Date]),
    MaxDate = List.Max(#"Sorted Rows"[Date]),
    #"Added Custom" = Table.AddColumn(#"Expanded Next", "Status", each if [Resource] <> [Previous.Resource] and [Date] <> MinDate then "New" else if [Resource] <> [Next.Resource] and [Date] <> MaxDate then "Deleted" else "Existing"),
    #"Sorted Rows1" = Table.Sort(#"Added Custom",{{"Original Sort", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Original Sort", "Index", "Index.1", "Previous.Resource", "Next.Resource"})
in
    #"Removed Columns"