Forum Discussion
Total Sales for same month last year
Hi MikeEcho,
After reviewing your shared pbix file, I find that most of your dbdate values are DateTime type which has a time value that is not "12:00:00 AM". That's why most of them are not matched with the Dates in Calendar table(with a time value of "12:00:00 AM").
So I use the formula below to add a new calculate column called "dbdate2" to get only the Date part of dbdate column in data2 table, and recreate the relationship with dbdate2 column between these two tables, then all works as expected.
dbdate2 = DATEVALUE(data2[dbdate])
Here is the modified pbix file for your reference. :smileyhappy:
Regards
I tried that and it did not work. So I decided to setup a sample dataset using excel to test and see what is going wrong.
The problem seems to be related to the Calendar table as it is obviously not doing the relationship as I expected. I am obviously doing something wrong, or missing something.
Any help appreciated.
Not sure how to attach my PowerBi and dataset it is located here
http://www.24web.co.za/powerbi/datanew2.xlsx
http://www.24web.co.za/powerbi/daterelationshiptest.pbix
Microsoft EmployeeHi MikeEcho,
After reviewing your shared pbix file, I find that most of your dbdate values are DateTime type which has a time value that is not "12:00:00 AM". That's why most of them are not matched with the Dates in Calendar table(with a time value of "12:00:00 AM").
So I use the formula below to add a new calculate column called "dbdate2" to get only the Date part of dbdate column in data2 table, and recreate the relationship with dbdate2 column between these two tables, then all works as expected.
dbdate2 = DATEVALUE(data2[dbdate])
Here is the modified pbix file for your reference. :smileyhappy:
Regards
Awesome v-ljerr-msft that worked and thanks for the explanation, makes sense why it did not work before. I will watch for this type of thing going forward ;)