Forum Discussion
Using another table (e.g., holidays) to filter a calendar table
I am trying to use a holiday table to filter a calendar table that already filters other tables and measures. When the applied filter is both directions, the calendar table correctly filters the holiday table. But the filter will not propagate in the other direction. Any suggestions?
This is the calendar table:
Calendar =
VAR MinYear = YEAR ( MIN ( 'Lista Preventivi e Prenotazioni e Cancellazioni'[DataAcquisizione] ) )
VAR MaxYear = YEAR ( MAX ( 'Lista Preventivi e Prenotazioni e Cancellazioni'[Partenza]) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Year", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] ),
"WeekNO", WEEKNUM ( [Date], 16 ),
"WeekDay", IF(WEEKDAY ( [Date], 1 ) = 7, WEEKDAY ( [Date], 1 ) - 6, WEEKDAY ( [Date], 1 ) + 1),
"StartofWeek", [Date]+1-WEEKDAY([Date]-6)
)
I have added a key to this table with the following calculated column, which looks up the correct key in a third, unrelated table:
Order_ = CALCULATE(MAX(Holidays[order_]), FILTER(Holidays, 'Calendar'[Date] < Holidays[Data fine] &&'Calendar'[Date] >= Holidays[Data inizio]))
Here is one year's worth of data from the Holidays Color table, which I want to use to filter the Calendar table using Order_ as the key (this table was created by duplicating the quers for the Holidays table mentioned above and then adding the color info):
| Anno | Festività | Data inizio | Data fine | Commenti | Order | Color Code | Color Code - Copy | Order_ |
| 2025 | Settimana Pasqua 1 | 12.Apr.25 | 19.Apr.25 | 1 | #CCCCCC | CCCCCC | 2025001 | |
| 2025 | Settimana Pasqua 2 | 19.Apr.25 | 26.Apr.25 | Pasqua 20/04 e Coinciveda con Tulipani 1 e Venerdì 25/04 | 2 | #CCCCCC | CCCCCC | 2025002 |
| 2025 | Tuliani 1 | 19.Apr.25 | 26.Apr.25 | Coincideva con Pasqua 2 e Venerdì 25/04 | 3 | #A0D1FF | A0D1FF | 2025003 |
| 2025 | Tuliani 2 | 26.Apr.25 | 03.May.25 | Giovedì 01/05 | 4 | #41A4FF | 41A4FF | 2025004 |
| 2025 | Tuliani 3 | 03.May.25 | 10.May.25 | 5 | #A0D1FF | A0D1FF | 2025005 | |
| 2025 | Settimana Ascensione | 24.May.25 | 31.May.25 | Giovedì 29/05 | 6 | #AEFDE4 | AEFDE4 | 2025006 |
| 2025 | Pre-Pentecoste | 31.May.25 | 07.Jun.25 | Lunedì 02/06 | 7 | #EFB5B9 | EFB5B9 | 2025007 |
| 2025 | Pentecoste 1 | 07.Jun.25 | 14.Jun.25 | 8 | #DE6A73 | DE6A73 | 2025008 | |
| 2025 | Pentecoste 2 | 14.Jun.25 | 21.Jun.25 | 9 | #DE6A73 | DE6A73 | 2025009 | |
| 2025 | Festa Vino 1 | 27.Sep.25 | 04.Oct.25 | Venerdì 03/10 | 10 | #717BC5 | 717BC5 | 2025010 |
| 2025 | Festa Vino 2 | 04.Oct.25 | 11.Oct.25 | 11 | #717BC5 | 717BC5 | 2025011 | |
| 2025 | Vacanze Danesi | 11.Oct.25 | 18.Oct.25 | 12 | #F0E199 | F0E199 | 2025012 | |
| 2025 | Halloween 1 | 25.Oct.25 | 01.Nov.25 | Venerdì 31/10 | 13 | #F5C4AF | F5C4AF | 2025013 |
| 2025 | Halloween 2 | 01.Nov.25 | 08.Nov.25 | 14 | #F5C4AF | F5C4AF | 2025014 |
MJEnnis From your last image, it does look like it is having an effect. Everything other than the 04/04/2015 row is all 0's so something is going on. If you want rows to not show up you could use a Complex Selector. The Complex Selector - Microsoft Fabric Community
Figured it out! Your complex selector post was helpful, but the solution was much simpler in my case. I have a measure that calculates the total number of any given product available on any given calendar day, considering the start and end season for each product. All I had to do was tell my existing measures to return a blank if the [units available] measure was blank. Works like a charm, as you can see below. Still have no clue why it replaces all other dates with 0 without this condition. But not going to rack my brain anymore... 😄 Thanks again! Just chatting with you seems to lead me to a solution everytime!
New measure (guess I will test if the first condition is redundant now, since it already passes through [units available]):Vacancy = Var OPEN_Date = MIN('Lista Tipologie delle Risorse'[InizioStagione]) Var Close_Date = MAX('Lista Tipologie delle Risorse'[FineStagione]) Var d = MAX('Calendar'[Date]) VAR vacancy_ = [Units Available] - [Occupancy] RETURN IF(d < OPEN_Date || d > Close_Date, BLANK(), IF(ISBLANK([Units Available]), BLANK(),vacancy_))
Screenshot of it working:
8 Replies
- MJEnnis
Resolver III
Note that the year range in the calendar table is currently 2014-2025.
- Greg_Deckler
Community Champion
MJEnnis I'm not quite sure what you are trying to do here. You can get a list of date values using DISTINCT from the holiday table and use the IN operator to FILTER the dates table. But honestly, I'm not exactly sure what you are trying to do here.
- MJEnnis
Resolver III
Hi Greg_Deckler!
I guess I am being intentionally vague. Helping my wife, so the data isn't mine and I cannot share too much of it. 😞
Maybe some images will help?
Below are the three tables in the model with the relationship I want. The relationship was created as described above: many to one, single, unique key, which was added to the calendar table with the above calculated column. Note that the calendar table is related to several other tables not shown and is also involved in several measures, though all but one of those relationships are inactive in the model.
When I filter visuals populated with measures and data from tables related to the calendar table, everything works properly. For example, as shown below, using the week number from the calendar table filters measures/visuals as expected.Also, if I change the direction of the relationship shown above to both, then the calendar table does filter the holiday colors table, as expected. For instance, highlighting any week that corresponds to the first week of the Easter holiday reduces the legend visual to just that holiday label, as shown here:
The problem is that the holiday colors table does filter anything. As shown below, removing other filters and selecting the second week of Easter has no effect on the visual or measures. I do not understand why...- Greg_Deckler
Community Champion
MJEnnis From your last image, it does look like it is having an effect. Everything other than the 04/04/2015 row is all 0's so something is going on. If you want rows to not show up you could use a Complex Selector. The Complex Selector - Microsoft Fabric Community