Strange Calculation behaviour on slicer where no data exist.

[kyrpav]

Hi i have a strange behaviour of the calculation function where i am do the sum of all hours recorded in a timesheet system.

The raw data includes hours until now since noone writes timesheet hours for the future ( or at least the raw data do not include future records) 

There is a visual where i just show the value of all approved hours in timesheet.

The function used is :

TS approved records = CALCULATE(sum(timesheet_raw[hours]),timesheet_raw[app_statuspm]=1)

In order this TS data to be synched with other data in the report from other sources, all tables where there is a data they have a calendar reference column that calculates the first day of that month.

Also there is a calendar table that is coming directly from database that has all these dates (one per month ) and the complete list of other required columns.

So we have a calendar reference table that is connected to the timesheet table with this referece date that is the first day of each month. The connection is one direction from the calendar to the timesheet.

When in the main visual place the total hours i am geting on value around 408k but when i am adding a slicer with the years the data change:

* When selecting all years i get the same value as the slicer was not there (normally and as expected)

* When i deselect 2025 and 2026 the total hours chage and decrease 1-2k though in the raw data there are no dates for these years

 

  

The calendar reference column from the Timesheet table since it has relationship on it it can not have date hierarchy.

There are other issues though. Implementing sums per year using the calendar reference table for the time or the timesheet calendar and adding inside there sum of hours from timsheet and the function that calculates then we get the same numbers per year per visual. but we should expect that these 2 visuals that have the dates from different tables will be the same. The result is that the total of all years is the same but the year distribution is not .

 

  

[danextian]

Hi @kyrpav 

It seems like a case of auto-exist causing unintended results.

https://community.fabric.microsoft.com/t5/Desktop/DAX-is-not-really-ignoring-filters-well/m-p/429419... 

https://learn.microsoft.com/en-us/power-bi/transform-model/value-filter-behavior