Calendar slicer problem

[TaoW]

Hi, everyone
  I'm a new user of PBI, but recently i met a problem i can't understand.

  I get the data from semantic model built by IT teams. Because the calendar period is longer than order time, so i add an extra computed column in calendar to judge if the date is within order time, then i put it into the visual as a filter condition. Strangely, when i choose  column field "Year" as the slicer field , the filter works well, but when i choose the field "Year" under "date hierarchy" built by IT, slicer failed. It still shows the whole years.  I don't know why i can't upload the pic successfully.
  please help me solve the problem. thanx!

[Anonymous]

Hi @TaoW ,

Create a separate calculated column for the year field as a filter instead of the year field in the date hierarchy.

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TaoW]

yes, the pic2 is  using the field "year" and it can work. But I also need to use other time dimensions, so it'll be convenient to use date hierarchy directly. i just want to know why the "year" in "date hierarchy" hasn't been effected by the calculated column.

ps: my calculated column:

period check = 'Global - Calendar'[Date] <=MAX('Global - Daily Sales'[OrderDate])&&('Global - Calendar'[Date]>=MIN('Global - Daily Sales'OrderDate]))

[Anonymous · Accepted solution]

Hi @TaoW ,

The built-in date hierarchy, which is automatically generated by Power BI, breaks down date fields into year, quarter, month, and day hierarchies. This hierarchy may cause filters to behave less than expected in some cases. Specifically, when you use the built-in date hierarchy, Power BI may not correctly apply filters in calculated columns. This is because each level in the built-in hierarchy (e.g., year, quarter, month) is processed independently, rather than applying filters as a whole.

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajendraongole1]

Hi @TaoW -Instead of using the built-in Date hierarchy, you can manually create a hierarchy from the individual fields (Year, Quarter, Month, etc.) in the model and ensure that your computed column is part of this structure.Use the individual "Year" field as the slicer (instead of the hierarchy), or make sure your filter logic applies at the date column level, from which the hierarchy is built. This might involve using a calculated column that tags dates according to whether they fall within the valid "order time" and apply that tag to your date column.

Default dynamic Date Slicer and also custom select Dates in PowerBI | MiTutorials (youtube.com)

Power BI - Build Your Own Custom Date Range Slicer (youtube.com)

Create Hierarchy Slicer in Power BI Desktop (youtube.com)

Hope this helps.