Forum Discussion

bsz412's avatar
bsz412
Helper III
4 years ago

Calendar table strange behavior

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. 

Need Help

8 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    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's avatar
      bsz412
      Helper III
      4 years ago

      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

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    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's avatar
      bsz412
      Helper III
      4 years ago

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

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    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.

  • bsz412's avatar
    bsz412
    Helper III
    4 years ago

    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's avatar
      johnt75
      Super User
      4 years ago

      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

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    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