Forum Discussion

MJEnnis's avatar
MJEnnis
Icon for Resolver III rankResolver III
1 year ago
Solved

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):

AnnoFestivitàData inizioData fineCommentiOrderColor CodeColor Code - CopyOrder_
2025Settimana Pasqua 112.Apr.2519.Apr.25 1#CCCCCCCCCCCC2025001
2025Settimana Pasqua 219.Apr.2526.Apr.25Pasqua 20/04  e Coinciveda con Tulipani 1 e Venerdì 25/042#CCCCCCCCCCCC2025002
2025Tuliani 119.Apr.2526.Apr.25Coincideva con Pasqua 2 e Venerdì 25/043#A0D1FFA0D1FF2025003
2025Tuliani 226.Apr.2503.May.25Giovedì 01/054#41A4FF41A4FF2025004
2025Tuliani 303.May.2510.May.25 5#A0D1FFA0D1FF2025005
2025Settimana Ascensione24.May.2531.May.25Giovedì 29/056#AEFDE4AEFDE42025006
2025Pre-Pentecoste31.May.2507.Jun.25Lunedì 02/067#EFB5B9EFB5B92025007
2025Pentecoste 107.Jun.2514.Jun.25 8#DE6A73DE6A732025008
2025Pentecoste 214.Jun.2521.Jun.25 9#DE6A73DE6A732025009
2025Festa Vino 127.Sep.2504.Oct.25Venerdì 03/1010#717BC5717BC52025010
2025Festa Vino 204.Oct.2511.Oct.25 11#717BC5717BC52025011
2025Vacanze Danesi11.Oct.2518.Oct.25 12#F0E199F0E1992025012
2025Halloween 125.Oct.2501.Nov.25Venerdì 31/1013#F5C4AFF5C4AF2025013
2025Halloween 201.Nov.2508.Nov.25 14#F5C4AFF5C4AF2025014



  • MJEnnis's avatar
    MJEnnis
    1 year ago

    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's avatar
    MJEnnis
    Icon for Resolver III rankResolver III

    Note that the year range in the calendar table is currently 2014-2025.

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity 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's avatar
      MJEnnis
      Icon for Resolver III rankResolver 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...