Forum Discussion
Understanding Date Table and How It Filters
I am a beginner with Power BI.
I have created a calendar table and marked it as such.
I have a simple history table which has various columns and a single date column (CreateDate).
I established a 1-many relationship between the calendar and the history table.
I added a column from the history table to a table visual.
I created a slicer with the calendar table for the fiscal year.
When I select a fiscal year from the slicer, the visual does not give me the correct data. There should be over 100,000 plus rows and I only get 2 rows.
Why is the calendar table not filtering the history table using the relationship? I thought I was beginning to understand filter context but obviously I am completely lost.
If anyone could point me to an article or video (visual learner so a video is best) that would be very helpful. All google search brings up are how to create a calendar table which I know how to do.
Thank you in advance.
Hi Txtcher ,
Thank you for reaching out to us on the Microsoft Fabric Community Forum.
Yes, the best and most efficient practice is to add a new column to your history table that contains only the date (without the time) and use that for your relationship to the calendar table.
This allows you to:
-
Maintain the original date/time values for analysis that needs time-level detail.
-
Use your calendar table (which only includes dates) effectively in slicers and visuals without needing a massive minute-by-minute calendar.
If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
-
7 Replies
Hello,
I would recommend reading up on the star schema. A helpful article can be found here:
https://learn.microsoft.com/en-us/power-bi/guidance/star-schemaCould you maybe share some screenshots of the relationship, and what the result should look like? It would be helpful to also see the date table and make sure that every date that corresponds to a fiscal year all have the fiscal year listed (like this:)
Date FY 6/29/2024 FY 2024 6/30/2024 FY 2024 7/1/2025 FY 2025 7/2/2025 FY 2025 Thank you for the article link.
Here are screen shots (this is just some sample data for testing and learning purposes).
Calendar table - yes it has at Fiscal Year for each date in the calendar:
The history table is simple (huge, but simple).
Relationships:
This seems like it should be pretty basic and simple, but ...I am clearly lost.
Hi Txtcher
You calendar table should be able to filter your HX table based on the images provided. Please check if:
- calendar covers all dates in HX
- relationship is between the same data types HX[CreatedDate] could be a datetime but appears as date only on data/table view. Note: Type any (ABC123) is also considered a text.
Hi Txtcher,
The most common reason your Calendar slicer isn’t “bringing back” all 100 000+ rows from your History table is that the two date fields don’t actually match, even though they look the same on the surface. Because,
History[CreateDate] is a true Date/Time (with hours, minutes, seconds),
Calendar[Date] is a Date only (midnight / 00:00:00)
When you build a relationship in Power BI between a Date/Time field and a pure Date field, the engine only “joins” on exact DateTime matches. So if only two rows in your history happen to have a CreateDate of, say, “2025-05-01 00:00:00,” you’ll only ever see those two rows when you filter by that date.
Also, try to go through the below link to understand more context:
Solved: why is my date filter is not filtering correctly? - Microsoft Fabric CommunityIf this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks!
Best Regards,
Maruthi
Thank you so much. This absolutely helps me to understand the problem. But the solution has me a little stumped. I need to keep the date and time values in the history table. So how do I build a calendar table with a start date/time of 1/1/2020 12:00:00 am through a dynamic end date/time of datetime.LocalNow()?
Or do I add a column to the history table in power query that extracts the date only and use that as my relationship column? (I need to keep the time values for other measures.)
Which is more efficient or best practice?
I am hoping the answer is to create a column in the history table that extracts the date only because I am having a hella time trying to write the m-code for a calendar table that includes all the minutes between 1/1/2020 - now.
Hi Txtcher ,
Thank you for reaching out to us on the Microsoft Fabric Community Forum.
Yes, the best and most efficient practice is to add a new column to your history table that contains only the date (without the time) and use that for your relationship to the calendar table.
This allows you to:
-
Maintain the original date/time values for analysis that needs time-level detail.
-
Use your calendar table (which only includes dates) effectively in slicers and visuals without needing a massive minute-by-minute calendar.
If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.
-
