Dates Table Relationship

[gerget]

Dear all, 

I am having problems with creating a working relationship with the dates table I created in Power BI and unfortunately could not find a solution in the forum that worked for me.

I have an invoices table, which stores every sale that has been made, with the columns

• createdon:                 date of the invoice
• totalamount_EUR:     invoice value

So there is multiple entries per day (-> createdon column has duplicates).

Since I need a dates table for many calculations, I set one up in Power BI and tried to create a many-to-one relationship between the "createdon"[invoices] column and the "date"[dates] column in my dates table: 
Relationships
Now, once the relationship is active, a report table that shows the values for "date" (drawn from the dates table) and "totalamount_EUR" only lists all the values separately: when there's an entry in the "date" column, the "totalamount_EUR" column is empty and, vice versa, where there's values in the "totalamount_EUR" column there is no corresponding values for "date".

When I create a report table with the "totalamount_EUR" column and the "createdon" (instead of "dates") column, this problem does not appear!
It looks like this:

Report Tables
Thus, I assume the relationship between createdon[invoices] and date[dates] does not work properly. 
The code I used to set up my dates table looks like this: 

Dates = 
  GENERATE (
    CALENDAR( DATE( YEAR( MIN(invoices[createdon]) ), MONTH(  MIN(invoices[createdon]) ), DAY( MIN(invoices[createdon]))), DATE( YEAR( MAX(invoices[createdon]) ), MONTH(  MAX(invoices[createdon]) ), DAY( MAX(invoices[createdon])))),
    VAR startOfWeek = 2    
    VAR currentDay = [Date]
    VAR days = DAY( currentDay )
    VAR months = MONTH ( currentDay )
    VAR years = YEAR ( currentDay )
    VAR nowYear = YEAR( TODAY() )
    VAR nowMonth = MONTH( TODAY() )
    VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
    VAR todayNum = WEEKDAY( TODAY() )
    VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
  RETURN ROW (
    "day", days,
    "month", months,
    "year", years,
    "month year", months & "/" & years,
    "day index", dayIndex,
    "week index", weekIndex,
    "month index", INT( (years - nowYear ) * 12 + months - nowMonth ),
    "year index", INT( years - nowYear )
  )
)

Does anyone have an idea how to fix this? Thankful for advice!

Best regards
gerget

[v-yuezhe-msft · Accepted solution]

@gerget,

Could you please share sample data of your invoices table here? 

As other's post, does the createdon column contain datetime values and you manually change the data type of the column to date? If so, the above issue will occur. You can create a calculated columns using dax below. Then create relationship between the new column and date column of dates table.

newCreatedon=DATE( YEAR(invoices[createdon]) , MONTH(  invoices[createdon] ), DAY( invoices[createdon]))

Regards,
Lydia

[Nirvay_KC]

Thank you @v-yuezhe-msft ! This was very helpful. In PowerBi Desktop (April 2020) , you could also use Date.From. 

[gerget]

Dear Lydia,

this actually worked! Thank you so much for your help.

Best, 
gerget

[dedelman_clng]

Check for time values on your Invoice table. You may need to strip/convert in PowerQuery or in the source data.

17 Aug 2018 09:00:00 <> 17 Aug 2018 00:00:00

Hope this helps

David