Forum Discussion

zoebranchtree's avatar
zoebranchtree
Regular Visitor
2 years ago

Calendar Table Quarter Filter

Have a bit of an interesting measure/calculated column I'm trying to build, and coming across some issues. In all honesty, this may not be possible, but figured I would post here before the full give up, haha.

 

Essentially, I am looking to filter each quarter out of my date table until a set date. I'm hoping to make this automatic without the need for a person to change the hardcode each quarter, so I don't want to hardcode the dates and want each quarter to show a month after the following quarter has started.

For example: Q3 of 2023 as a whole should not be a visible value/should not be visible in the data until Nov 1, 2023 (one month after the start of Q4). Of course, in this scenario, Q4 of 2023 should also not be visible until Feb 1, 2024.

 

I'm doing this for data integrity as I may not have all of the data for a quarter prepared until after the quarter has finished. My Calendar Table has a date column, a quarter number column, and a year column, and creates all values for the year on the first of the new year (dec 31st is entered into the calendar table on the 1st of january of the same year). I know this is also possible just by making a duplicate Calendar Table, but want to keep as much strain off my data model as possible, since it's already pretty hefty, in all honesty. (EDIT: I can't just change how the Calendar Table loads data due to other reports that are reliant on this data model and require all data available, hence trying to do it in a weird way with a measure or column.)

 

Hearty thanks for any help or advice given!!

1 Reply

  • Hi zoebranchtree 
    You can add to your date table, a flag column with a formula like :

    Show q = if('Calendar (2)'[Start of Quarter].[Date]<Today()-'Calendar (2)'[Days in Month],1,0)
    Then use this formula as a filter for your quarters' slicer

    I created PBIX with all the necessary columns on the calendar (it is with the needed m code including the start of the quarter and days in a month).

    You can download it from This link 

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