I am having this issue and wonder if anyone can help me with it. I have 3 data tables. As you can see, my Calendar_Cycles table does not have a individual date column, that is why I have connected Calendar_Days table with with ‘Period ID’ column.
I want to get sales per day and they are not coming out as I wanted. As you can see on the screenshot below. When I make a table with Period ID, sales data comes out accurate( Table 2), when I add the date from ‘Calendar_Days’ table, data comes as table 1.
Well, it’s difficult to provide the results efficiently without looking at the data. Could you please upload the working of your PBIX file so that members of our forum can assist you in a better manner?
If your file contains the sensitive data then you can hide that data. Below is the link provided for the reference where one of our expert @BrianJ had created a video about how to hide the sensitive data.
Could you please help to update a sample pbix file. As just seeing the model can suggest you should remove bi-directional relationship and make it 1-many so you data model should be — Date Days—- Calendar cycle —- sales Table.
@nipunadv Looks fine to me, a single PeriodID has multiple dates associated with it so when you slice data by dates you get more granular result, and when you slice by PeriodID all the related dates are grouped under 1 PeriodID hence less granular report.
You can verify this by creating a matrix and then including both periodID and Dates on row field.
Looks like no issue but can you show what output you are looking for – more granular result.
Also 1 suggestion is that you can remove the DateTime column and have only unique value in the Calendar_Days table if you are not using it or have no intension to use in future.
Here are some of the simple steps that I’ve followed in order to achieve this result -
Step 1: Added a Date Table into the model which is available under the “M Code Showcase” which is created by one of our expert member @melissa.
Step 2: The “Order Date” column which you’ve in your “Sales Products” table I’d split them into 2 columns i.e. “Order Date” and “Order Time”. Below is the screenshot provided for the reference -
Step 3: Then click on the “Close and Apply” button. And go to the “Data Model” view pane. And create a relationship between “Order Date” column and “Dates” column. Below is the screenshot provided for the reference -
In your Calendar Days table, why do you need multiple entries for the same day. this might be your issue why you getting wrong numbers. i would say you only need one for each day.
