Same result for all periods

[kg4u]

Hi,

You guys are so helpful, I need another assist.  I have a super simple easy measure, and I'm simply trying to show sales by month, and it's not working.  I get the same result for each period in my visual.  The visual is correctly displaying the values for the different segments, but it is not showing the sales by month.  It is showing the totals sales for the entire year.  The Total Sales for the entire year is also a problem, while the $19,103,341 number is the correct value for Year 2022, it's only September and the visual is showing a value for October, November and December which do not have data.  Ideally, I'l like Total Sales by Month by Segment in a single visual. 

I know you will suggest my data model might be the problem, and it might be. Here's a view of my data model.  I have a two-way relationship between Entry Month and Month and a two-way relationship between Entry Year and Year.  I also created a Month Year column in my Calendar Table to try and resolve this but I still received the same results for each month.

Please suggest how to resolve my simple problem.

Thank you so much!

[tackytechtom]

Hi @kg4u ,

Awesome that you are using a Calendar Dimension!

I'd suggest to connect your fact table with the Calendar table via Entry Date and Date.

Does it solve your issue then? 🙂 Let me know!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[kg4u]

The fact table is connected to the calendar table.  I have 2 two-way relationships between the fact table and the calendar table, as shown in the second snippet.

[tackytechtom]

Hi @kg4u ,

As @ce87 described, you should not create relationships in that way. Instead create the relationship on the most granular level namely from CalendarTable[Date] to FactTable[EntryDate]. This will result in a one to many relationship and should fix your issue.

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[ce87]

Since you have an Entry Date on your fact table, and an Date column on your Calendar dimension, you should be able to create the relationship using the two date columns instead of the month. 

Since the month is repeated every year you have a many-to-many relationship which is cuasing the problem.

Create a one-to-many relationship from the Calendar Table [Date] (1) -> Fact Date[Date] (*). Then in your visual drab the month and year columns from the Calendar Table instead of the Fact Table. 

This should accomplish what you are looking for.

[kg4u]

I did create the new relationship in the data model, but it did not resolve my problem.

I'm still getting the same result for each period.  I did remove the 2-way relationship between year and it did not resolve my problem. 

[tackytechtom · Accepted solution]

@kg4u remove the other many to many (2-way) relationship as well

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[kg4u]

Thank you so much!  Kind of thought it might be the data model.  It was way too easy of a calc not to be working 😉  You are awesome!