Understanding Date Table and How It Filters

[Txtcher]

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.

[maruthisp]

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,

1. History[CreateDate] is a true Date/Time (with hours, minutes, seconds),

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

If 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 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[Txtcher]

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.

[v-menakakota · Accepted solution]

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.

[ExcelMonke]

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

Could 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

[Txtcher]

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.

[ExcelMonke]

Hi @Txtcher, as some others have commented, it seems like you have everything set up correctly. I would recommend checking if you are linking the dates with the same format. I.e. make sure both columns are in either date, or date/time format. Sometimes this can be a little bit tricky and cause problems if they are not the same. 

A good way think about it is if one column has a date as (1/1/2025, 12:00:00am) and the other as (1/1/2025), they may seem the same but the relationship is not valid because the first column has a time element that it can't match to in the second column. Hope that makes sense and is what is causing your Power BI to not return the expected result! 

[danextian]

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.