Calendar table strange behavior

[bsz412]

Hi, 

I have a calendar table created as below: 

Calendar = var FullCalendar = ADDCOLUMNS(CALENDAR(MIN(sales_purchase[Date]),MAX(sales_purchase[Date])),"Month Number",MONTH([Date]),"Year",YEAR([Date]),"Year-Month",LEFT(FORMAT([Date],"yyyyMM"),6),"Year-MonthName",YEAR([Date]) & " " & Format([Date],"MMM"))

return

SUMMARIZE(FullCalendar,[Month Number],[Year],[Year-Month],[Year-MonthName])

output is: 

The problem is, if I use the date as hierarchy it show it till end of 2022, all month, even though those months are not in the calendar table

I need to have this drill down possibility from year to month, so I would like to understand why is this happening and how can I fix it. I appreciate your thoughts on this. 

[v-luwang-msft]

Hi @bsz412 ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[bsz412]

I have the same issue with a different calendar table as well 😞 I found out that the root of the issue is that the date hierarchy does not react to the date slicer. How could I fix that? @johnt75 @HotChilli 

[johnt75]

Try creating a proper date table instead of using the built-in one. You can use the code you have already but get rid of the SUMMARIZE, you want to return the full table. When you've created it, you can right-click on it and choose "Mark as date table". You'll then be able to create your own date hierarchy which should solve your problems

[HotChilli]

I think there's a conflict between the calendar table and the auto-created date table.  There is no Month table in the created table but it appears in the field well for Axis.  Click on the Year-MonthName caret and see if it lets you pick your hierarchy.

[johnt75]

I think Power BI is using an automatically created calendar table, which always has dates covering entire years. If you add the column I suggested you should be able to filter out all the dates after today

[bsz412]

unfortunately it doesnt work. I will try to create a new calendar table

[johnt75]

Add another column onto your Calendar table called Today or Before, defined as [Date] <= TODAY(), then you can use that as a filter on the visual to only show when true.

[bsz412]

in the calendar table I have data only till Feb 2022. on the chart when i use my date column as hierarchy, it does not react to my period slicer either. Regardless of the period slicer selection X axis always shows date from jan 2021 till dec 2022. Period slicer uses the exact same date column. When I use this date column on X axis without hierarchy, it works, but then I lose the drill down option