Power Pivot Relationship Between Tables Done, Still Getting Duplicate Values, Suspect Date is Text.

[John_H1450]

H Folks, 

I've been at this all day.

Because of relationship issues (and I suspect the underlying reason is the dates, while appearing as dates in the file, are actually still text I believe), I've created a simple bridge table between income vs expenses tables so that when I create a simple Pivot Tables, they have a relationship.

When i try and create a simple expenses vs income by month, one of the two will will have duplicate values.  Through hours of research, I believe the issue is the dates, while appearing as dates, are actually text, causing the table to have dup values.

Fyi - I'm on windows 10 Laptop bought in Thailand, checked the system settings as best i know how they can affect dates when importing .csv values from another region.  Change date type via Locale, no go.  Tried DATEVALUE in Excel and that came back with a VALUE! error.  

I'm out of options and have nowhere else to look, hoping one of the pros out there can diagnose the issue.  Attached is a test file with all elements looking like they should work, but not yet.

Thank you for any responses.

[#2]

Something strange happened - when I just tried to login to my microsoft account, the language was all Pinyin. I'll bet the operating system is seeing the .csv date as text. Taking the computer in Tomorrow.

[#3 · Accepted solution]

@John_H1450 

You need Calendar table (now is usually named as Date). I generate it in Power Pivot, better do that by Power Query, you may find a lot of templates. Model will be 

and PivotTable is

[#4]

I can't believe this! That is something that will be so helpful, I can't wait to apply it to the model I have to re- build now.

I googled and studied this problem from every angle. This is a "bridge table" yes?

Why did my account bridge table not work where your yours does? Is it an applicable solution for every type of relationship table

Just trying to figure out the logic.

Thank you again, for me who is just learning dax measures to add more meaningful context to power pivot tables, this will be a deal changer and I can move forward instead of spending the whole day googling on one problem.

[#5]

Not only that, looks like I need to study power pivots in general more.

[#6]

@John_H1450 

On this journey I'd recommend to check Home - SQLBI where everyone who deals with DAX, from beginner to Pro, could find a lot of useful information. There are a lot of other great resources, but this is one of the best.

[#7]

Done, signed up! In the meantime I'll study up on the hows and why's of what you did - its what you put where, that they are not table columns but functions - I think theres some interesting stuff there for me. Again, many thanks!