Changing values in a dimention table year by year

Bassehave — Mon, 16 Oct 2023 07:45:59 GMT

Hello gurus,

I have run into an issue regarding our data and I'm not quite sure how to fix it in the best / most efficient way, so hopefully you will be able to offer your support.

The issue is that we have a dimention table that we use / have used for years, but now some of these values have changed and now you might say, then change the values in the table, but this is where the problem is. If I change the values in the data, all of my old data will have the new values and not the value which it had "back in the day".

Zip Code	Store
1	C
2	B
3	A
4	A
5	B
6	A

This is a basic lookup table, where Zip Code is unique and it returns the store that belongs to this Zip code.

When looking at the table in 2023, it now looks like this: (A new store has opened and some of the Zip codes, now belong to the new store "D")

Zip Code	Store
1	C
2	B
3	D
4	D
5	B
6	A

But I don't want to loose the data integrity of what it looked like back in the day.

So now on to the solution, I was thinking of just adding a "Year" column and when linking the tables, then link by both Zip Code and Year. For this solution I see two ways of doing it:

Zip Code	Store	Year
1	A	2022
2	B	2022
3	C	2022
1	D	2023
2	B	2023
3	C	2023

Problem here is, now the Zip Code column is no longer unique. The other option is:

Zip Code	2022	2023
1	C	C
2	B	B
3	A	D
4	A	D
5	B	B
6	A	A

Now we are back to the Zip Code, being the unique table, but the problem here is that I can't merge tables based on a changing column header.

Do you know of a solution for this problem?

I have the Year and Zip Code column in both tables already.

Thanks in advance,

Re: Changing values in a dimention table year by year

some_bih — Tue, 17 Oct 2023 06:40:56 GMT

Hi Bassehave check link below as possible two solutions for your case, part

Same store sales with snapshot and

model view for

FIGURE 6 The data model requires a weak MMR relationship between Date and StoreStatus.

https://www.daxpatterns.com/like-for-like-comparison/

Did I answer your question? Kudos appreciated / accept solution

topic Changing values in a dimention table year by year in DAX Commands and Tips

Changing values in a dimention table year by year

Re: Changing values in a dimention table year by year

Same store sales with snapshot and