Calculation groups to switch between dates not working as expected

[gustavog80]

Hi all,

I'm trying to dynamize a report to give user the hability of switching between different dates to filters some data. I read some ideas and seems that the best option is set 3 relationships to calendar Table ( one for every field date I wanted to allow filter), one active, two inactives, and enable / disable any of them using USING RELATIONSIPS in my measures, so I can get the numbers filtered by any of these dates.

I created 3 calculations group items like this:

Tenant Lease Date = 
                CALCULATE(
                        SELECTEDMEASURE(),
                        USERELATIONSHIP(sample_dataset[Tenant Lease Executed],
                                        'Calendar'[Date]
                                        )               
                        )  

In the upper table, u can see the correct numbers without using slicers and calculations groups.

If u choose in the slicer Tenant Lease, and filter one year, for example 2025, number change ( in both tables, upper and down) so think something is wrong with my approach:

So I expected, when chossing the desired date field, get the same values than filtering the date manually (without using the dynamic slicer), but numbers do change. Am I doing st wrong ? Cant find there is the issue



I uploaded here the dashboard:

https://drive.google.com/file/d/104mHmO8CG-3kU1MhU3L8GI--8bfeH0M3/view?usp=sharing

Thanks in advance,
Regards

[OwenAuger · Accepted solution]

Hi @gustavog80 

The issue appears to be that the columns sample_dataset[Tenant Lease Executed] and sample_dataset[Forecast Month] each contain values with non-zero time components, so they are not pure "dates".

This is causing inconsistent results since the relationship with 'Calendar' relies on having date values with no time component in order to filter correctly.

I would recommend changing the types of both columns to date in either the source file or Power Query.

While those columns have been changed to Data type = Date in the Power BI model interface, this does not remove the time component of the values, but merely hides it. Date and DateTime are really the same underlying type when loaded to the model. To be certain of "dates" being loaded, the type must be changed upstream, in Power Query or source.

You can verify that this is the issue by running a query like this in the DAX Query view:

EVALUATE
	SELECTCOLUMNS(
		SUMMARIZE(
			sample_dataset,
			sample_dataset[Tenant Lease Executed],
			sample_dataset[Forecast Month]
		),
		"Tenant Lease Executed Formatted", FORMAT(
			sample_dataset[Tenant Lease Executed],
			"yyyy-MM-dd HH:mm:ss"
		),
		"Forecast Month Formatted", FORMAT(
			sample_dataset[Forecast Month],
			"yyyy-MM-dd HH:mm:ss"
		)
	)
	ORDER BY
		[Tenant Lease Executed Formatted],
		[Forecast Month Formatted]

[gg80]

Thanks @OwenAuger ! And thanks for the detailed explanation. At first I thinked was a problem in the dataset I exported from SQL Server to csv, but I checked and seems that exactly the one u described is the problem even in the original dataset. I switched the 3 fields to pure "Date" and numbers do match now.

Thanks again,
Regards!