cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Y2Y change based on data presence in table

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-1Does ID exist in YearY2Y change
YesYesExisting
YesNoPhased Out
NoYesPhased 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 🙂 )

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper II
Helper II

Re: Y2Y change based on data presence in table

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.
 

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Y2Y change based on data presence in table

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.

 

 

Highlighted
Helper II
Helper II

Re: Y2Y change based on data presence in table

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 🙂

Highlighted
Helper II
Helper II

Re: Y2Y change based on data presence in table

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.
 

View solution in original post

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors