Strange Issue - My Date table is showing the same date for all rows in visuals

[minimalist]

Sorry if this is in the wrong place but I don't know my way around.

Look at this matrix, specifically the Date field.

As you can see, every row is showing the same date but despite appearances, it is filtering the Order table correctly. 

I only noticed this a few hours ago and I can't figure out what is going on. I don't use the daily date field in many visuals so don't know when this first started happening. 

My model has only one date table and it is marked as a date table. This visual above is only using one simple SUM() aggregation. The two other date fields shown are coming from the same Date table. 

This is what my Date table looks like. Everything looks as it should in the Lakehouse. The Date table created in my Dataflow is from 01/01/2018 to 31/12/2025.

Any thoughts?

[minimalist · Accepted solution]

As I original posted this problem in two places (I know, I know), I'm cross posting the solution in both places...

I just wanted to close this topic as three months later I solved it and it was surprisingly simple. 

We need dates in dd/mm/yyyy format and as they often switching to mm/dd/yyyy, we customise our date format in our PBI model. You'll notice the example date at the bottom of the pane below is the same erroneous date which appeared on every row of my visual. The underlying date was still correct but it was being explicitly formatted as 14/03/2019, not merely dd/mm/yyyy.

I changed the date format back to one of the standard options and the problem went away.

I still don't really know how this could happen. I would just urge caution when using custom date formats.

[v-agajavelly]

Hi @minimalist ,

Just checking in one last time haven’t seen you back in a while. As per the Microsoft Fabric Community Forum guidelines, we’ll go ahead and close this thread for now. If your issue has already been resolved, that’s great to hear.

But if you still need help down or anything else feel free to create a new post anytime. The community is always here to support you.

Regards,
Akhil.

[v-agajavelly]

Hi @minimalist ,

We apologize for the inconvenience you are experiencing.
To assist you effectively, could you please share sample data that fully illustrates your issue or question? We kindly request that the data be provided in a usable format (rather than as a screenshot) and that it does not contain any sensitive information or unrelated content.

Additionally, it would be helpful if you could include the expected outcome based on the sample data you provided.

Thank you for your cooperation!

Regards,
Akhil.

[v-agajavelly]

Hi @minimalist ,

Thanks for sharing the updated details and sample, this clears up what’s happening. You're using this measure,

Date_Test = MAX(Dates[Date])

This will always return a single value across the entire visual, because it’s an aggregated measure. In your example, the visual isn’t being sliced by Dates[Date], so the measure just evaluates once and shows 14/03/2019 repeatedly.

If you want to see the actual Date values row by row. Use Dates[Date] as a column (not as a measure). For example. Drag Dates[Date] directly into the table visual’s rows. Don’t wrap it in MAX, MIN, or any aggregation. That will ensure you see each row’s actual date value from the date table.

If Dates[Date] is still showing just one value even when used as a column:

• Double-check that you’re not using a measure in place of the column.
• Make sure there’s no measure in the table that’s overriding the visual context.
• Try placing just Dates[Date] in a new table visual without any measure you should see your full date list.

If this response helps, consider marking it as “Accept as solution” and giving a “kudos” to assist other community members.

Thanks,
Akhil.

[minimalist]

Hi Akhil

Thank you for your input but I don't think your reponse is correct.

I am using Date[Dates] as a column and it is providing filter context to Date_Test = MAX(Dates[Date]).

As you can see at the bottom, the MAX date for my Dates table is 31/12/2025. 

The measure is correct and the Date column is also providing the correct filter context. The problem is that the Date column is displaying the wrong date. Behind the scenes, it is working correctly.

On my Dates table I have other columns based on the main Date column. They are all working correctly and display correctly.  Here is another visualisation showing addtional date fields which further illustrate the problem. Both visualisations in this post come from the Date table only with no relationships involved.

[v-agajavelly]

Hi @minimalist 

Thanks for the detailed explanation totally understand how confusing this can be, especially when the filters work but the Date column just repeats the same value. From what you described, this seems like a visual context issue the matrix is likely evaluating the Date field outside of its intended row context. To assist you effectively, could you please share sample data that fully illustrates your issue or question? We kindly request that the data be provided in a usable format (rather than as a screenshot) and if it does not contain any sensitive information or unrelated content.

Additionally, it would be helpful if you could include the expected outcome based on the sample data you provided.Thank you for your cooperation.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Regards,
Akhil

[minimalist]

Thank you for your response.

As I thought I posted this in the wrong place, I also posted the problem here:

 https://community.fabric.microsoft.com/t5/Desktop/Strange-Issue-My-Date-table-is-showing-the-same-da... 

I didn't mean to create an issue with duplication but I will try to maintain both threads. I don't believe there is any confusion in my model.

For testing, I deactivated all relationships and created an additional measure in my Dates table:

Date_Test = MAX(Dates[Date])

Then I created another visualisation using the Dates[Date] field and only this measure. I don't know how it is possible for the Date field to display the 14/03/2019.

What is even more unusual, if I reconnect the relationship between Orders and Dates, all other columns in my Dates table filter the Orders table, except the Dates[Date] field.

The result I expect is the Date column to show the Date that is in the table, not 14/03/2019.

As suggested, I attached sample data. This an extract from my Date table as it is too large to paste all of it.

Date	MonthYear	MonthYearSort	QtrYear	YearQtr	DayofWeek	DayOfWeekName	WeekofYear	YearWeek	Month Name	Year	MonthType	Month	YearType
01/01/2023	Jan-23	202301	Q1-2023	20231	6	Sunday	1	202301	January	2023	Jan-23	1	01/01/2023
02/01/2023	Jan-23	202301	Q1-2023	20231	0	Monday	2	202302	January	2023	Jan-23	1	02/01/2023
03/01/2023	Jan-23	202301	Q1-2023	20231	1	Tuesday	2	202302	January	2023	Jan-23	1	03/01/2023
04/01/2023	Jan-23	202301	Q1-2023	20231	2	Wednesday	2	202302	January	2023	Jan-23	1	04/01/2023
05/01/2023	Jan-23	202301	Q1-2023	20231	3	Thursday	2	202302	January	2023	Jan-23	1	05/01/2023
06/01/2023	Jan-23	202301	Q1-2023	20231	4	Friday	2	202302	January	2023	Jan-23	1	06/01/2023
07/01/2023	Jan-23	202301	Q1-2023	20231	5	Saturday	2	202302	January	2023	Jan-23	1	07/01/2023
08/01/2023	Jan-23	202301	Q1-2023	20231	6	Sunday	2	202302	January	2023	Jan-23	1	08/01/2023
09/01/2023	Jan-23	202301	Q1-2023	20231	0	Monday	3	202303	January	2023	Jan-23	1	09/01/2023
10/01/2023	Jan-23	202301	Q1-2023	20231	1	Tuesday	3	202303	January	2023	Jan-23	1	10/01/2023
11/01/2023	Jan-23	202301	Q1-2023	20231	2	Wednesday	3	202303	January	2023	Jan-23	1	11/01/2023
12/01/2023	Jan-23	202301	Q1-2023	20231	3	Thursday	3	202303	January	2023	Jan-23	1	12/01/2023
13/01/2023	Jan-23	202301	Q1-2023	20231	4	Friday	3	202303	January	2023	Jan-23	1	13/01/2023
14/01/2023	Jan-23	202301	Q1-2023	20231	5	Saturday	3	202303	January	2023	Jan-23	1	14/01/2023
15/01/2023	Jan-23	202301	Q1-2023	20231	6	Sunday	3	202303	January	2023	Jan-23	1	15/01/2023

[maruthisp]

Hi @minimalist,

Can you try below DAX expression as per the original post description:
VAR Date_PreviousMonth = EOMONTH(EDATE(SELECTEDVALUE('POC Data'[MonthEndDate]), -1), 0)
VAR Backlog_PreviousMonth =
CALCULATE(
SUM('POC Data'[IFRS Backlog]),
ALL('POC Data'[MonthEndDate]),
'POC Data'[MonthEndDate] = Date_PreviousMonth
)
RETURN
Backlog_PreviousMonth

Please let me know if you have any comments. Thanks.
Best Regards,
Maruthi

[minimalist]

Hi Maruthi,

Thank you for the reply but I don't see how I can use this measure with my model.

Your measure only refers to one table and my issue is related to how the Dates column of my date table (that the relationships are based on) is showing incorrectly when filtering my Orders table.

Despite the data appearing incorrectly, the correct filter context is being applied and the result in the visual is showing the the correct value (for the date that is not shown)

[minimalist]

Perhaps this is the wrong place for this problem. 

I just reposted it here: https://community.fabric.microsoft.com/t5/Desktop/Strange-Issue-My-Date-table-is-showing-the-same-da... 

[minimalist]

I also just noticed that my MonthType has a This Month / Next Month error. The shame.