Y2Y change based on data presence in table

[giogiogio]

Hello everybody!

I have a table containing IDs and Dates. I want to track IDs Y2Y change, with 3 possible outputs:

Does ID exist in Year-1	Does ID exist in Year	Y2Y change
Yes	Yes	Existing
Yes	No	Phased Out
No	Yes	Phased In

In Power Query I created 2 tables that I load in my Power BI model:
- T1 with unique values for IDs (with other attributes of each ID not changing during the years)
- T2 with granularity ID-Year (that also includes other attributes of that ID that can change during years - e.g. location) but only for years in which the ID is present.

I then created in T1 n-calculated columns (n is the number of years of the model -1) tracking the Y2Y status change with respect to the previous year reflecting the output of the table above: existing, phased in, phased out.

I want now to create a new calculated column in T2 summarizing the Y2Y status, at granularity ID-Year.
The problem is that I cannot trace "Phased Out" status as, by definition, this condition is happening if an ID was present at Year-1 but not during the following Year!
How can I increase the granularity of the years for each IDs to the following year or even to all years of the date span?

Note: I know the method to track Y2Y changes I implemented is a bit tricky, but it was the easier for me (I am not a noob in DAX) and I also would like to visualize data in the Sankey Visual, and this was the easier way - putting Year-1 in the source and Year in the destinantion (for the time being i didn't found a better alternative, but this will be one of the following questions 🙂 )

[giogiogio · Accepted solution]

If it can help, this is the solutions I found:

1) Crossjoin in DAX, with Years and ID as inputs. It creates a table of granularity ID-Year. To link this new table with table T2 (my original ID-Year table, missing some ID-Year entries) I thought about creating a new column in both as the concatenation of ID-Year. However this trick was not working with RELATED command, maybe because the table was too big.

2) Merge command in PowerQuery: 
a) Added a dummy coulumn with all values set to 1 to both ID and Year tables
b) created table T3 by merging the two tables ID and Year with full outer using the new dummy columns
c) created a "link" column in T3 and T2 concatenating ID and Year
d) merge T3 and T2 with left outer using the new link columns to "copy" to T3 the columns of interest from T2
e) load the new table T3 in the BI instead of the old T2. Same data as T2, but with more rows to have granularity ID-Year.
 

[dax]

Hi @giogiogio, 

I am not clear about your data structure, if possible could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[giogiogio]

Hi @dax  , thanks for your support.

At this link you can find a very simplified version of the file I am working on: https://1drv.ms/u/s!AuVTlITP31UgikIvjZa-TcK_G9ri 

"Product List" is what I was mentioning as "T1" and "History DB" is "T2".

Just to recap the steps done:
1) In the Product List table, for every year, I created a year-to-year comparison column of the "presence" of an ID in table History DB. 
This way I am able to track changes with respect to "year-1", including tracking the exit from service of a product the year after.
2) To use these data in visuals I need to log them back in table History DB: here I have the problem that, if I want to track a product that exited service, I need an entry for that product also for the "year after" it exited service.
    a) Y2Y_status_1 is the right way to show Y2Y data changes, but I am not able to track "Exit service" as said
    b) Y2Y_status_2 is a workaround I found, but I don't like it for two reasons: 1) I have to itroduce the "In & Out" flag, 2) "Exit service", as said are shown the year before they exited service, and this is not correct.

To solve this, my idea was to create a third table with the following granularity: a row for every year (starting at the first year of my snapshot and ending at the last year of the snapshot) for every ID I have.
This way I can apply step 2) to this third table and, having ID entry also for the year of exit from service, the issue is solved.
However I don't know how to do this (in Power Query preferably, or DAX as an alternative). 

Notes:
1) The DB is much bigger and complex, I need to keep the two tables to avoid repetition of "static ID attributes" during the years.
2) The steps above will be repeated also for "Country" to track location change over years, as well as other items that I have not included in the simplified model

Thanks 🙂