Forum Discussion
Relationship between two tables
The direction of the line shows the filter direction. So your data table is filtering the date table. That is backwards. The Date table is a DIM (Dimension) table and should always filter the FACT (or data) table.
Do this:Add this formula to your data table and call it Date. This is in Power Query. Add a new custom column. This is the formula.
if Date.EndOfMonth(Date.FromText([month] & "1, " & Text.From(Date.Year(DateTime.LocalNow())))) > DateTime.Date(DateTime.LocalNow()) then Date.AddYears(Date.EndOfMonth(Date.FromText([month] & "1, " & Text.From(Date.Year(DateTime.LocalNow())))), -1) else Date.EndOfMonth(Date.FromText([month] & "1, " & Text.From(Date.Year(DateTime.LocalNow()))))Now, that may not be right. What it returns is this (I hid columns for the sake of space)
Now connect that date column to the Date column in the Date table. It should be a one to many from the Date table pointing to the fact table.
Now the date table fields should be used in your visuals and will work.
If that is not the correctly calculated date, show me what it should be. Basically I said get the month name (jan, feb, mar) and make it the last day of month for this year, unless it is the future, then make it last year.
Yes. your relationship is backwards Dunner2020. It should be from the Date table (one) to the FACT table (many). You need a real date in your FACT table then link that to the Date in the Date table. This is why it is a flat line, the date table isn't filtering the other table. See the direction of the arrow?
And don't enable bi-directional. That doesn't always fix it and is the wrong approach here.
Without being able to see what kind of data is in the RY Month column, I cannot assist in making it a valid date. I'd need data, per below.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
edhans , I did not understand the direction of the line. The data was too big and the portal did not let me paste. So I have uploaded on one drive and pasted the link in the actual post.
The direction of the line shows the filter direction. So your data table is filtering the date table. That is backwards. The Date table is a DIM (Dimension) table and should always filter the FACT (or data) table.
Do this:Add this formula to your data table and call it Date. This is in Power Query. Add a new custom column. This is the formula.
if Date.EndOfMonth(Date.FromText([month] & "1, " & Text.From(Date.Year(DateTime.LocalNow())))) > DateTime.Date(DateTime.LocalNow()) then Date.AddYears(Date.EndOfMonth(Date.FromText([month] & "1, " & Text.From(Date.Year(DateTime.LocalNow())))), -1) else Date.EndOfMonth(Date.FromText([month] & "1, " & Text.From(Date.Year(DateTime.LocalNow()))))Now, that may not be right. What it returns is this (I hid columns for the sake of space)
Now connect that date column to the Date column in the Date table. It should be a one to many from the Date table pointing to the fact table.
Now the date table fields should be used in your visuals and will work.
If that is not the correctly calculated date, show me what it should be. Basically I said get the month name (jan, feb, mar) and make it the last day of month for this year, unless it is the future, then make it last year.
Thanks edhans for explaining the filter issue. I entered the date column in the fact table and connected with the dim table and it worked as expected. You are my saviour.
Great Dunner2020 - glad it helped. This is the beginning theory of the Star Schema, which is critical to most successful Power BI Models. MS has a page, and there are some good articles and books on it as well.