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.
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.