How to Fix the relationship with my date table?

[Libbyb23]

Hi, 

I have a date table. I have another table that is line level sales detail. I have a one to many relationship between Invoice Date to Date (in the date table)

I need the table to show the Date, Invoice Date, then the Sum of SalesIncFreight per day. Right now the invoice date is not summarizing. 

[andrewsommer]

Make sure your relationship is set up correctly (one way direction going from date to fact).  Then make sure that the two related fields are cast the same way (i.e. both are defined as dates). 

For your SalesINCFreight column are you using an implicit or explicit measures.  If you are using implicit (dragging the field to the well and letting it autosome) I would suggest creating an explicit measure as it is best practice. 

Please mark this post as solution if it helps you. Appreciate Kudos.

[Dineshyad]

Hi @Libbyb23 

Without changing the existing relationship (One to many) , you can edit the relationship and select the cross filter direction from "one to both", It will give the expected result.

Please refer the snap.

Thanks

[MFelix]

Hi @Libbyb23 ,

You refer that you have a relationship one to many from the invoice date to the calendar date if the relationship is exactly has you describe then you have the relationship in the incorrect direction you must have the one side of the relationship on the Calendar table and the many side on the invoice table.

If you have that relationship then you don't need to use the invoice date on your visual but if you want you can.

[Libbyb23]

hi, yes I have the one side on my calendar side but still not working properly. This is what happens when I remove the Invoice Date. it only show two dates and its weird.

[MFelix]

Hi @Libbyb23 ,

Sorry for the question but are you columns properly formatted? Are they both has dates on the semantic model?

[Libbyb23]

Hi, 

yes, they are both dates. It wont let me summarize the invoice date though?

When I do a date hierarchy is shows properly:

But when I change it to date, it shows by line level enough though the relationship is correct?

[MFelix]

Hi @Libbyb23 ,

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Libbyb23 · Accepted solution]

I figured it out!

the date field includes time information (e.g., a timestamp), Power BI might treat each unique timestamp as a separate day.

So I created a new date column
Invoice Date = 

DATE(YEAR('OVH Sales'[INVOICE DATE]), MONTH([INVOICE DATE]), DAY([INVOICE DATE]))


Then changed the relationship to this new date and now it works.