Connecting a calendar table to a specific dates table

[cdcphist]

Hi,

I have a Calendar() function generated table with all the dates between 2016 and now. I also have the following table:

Start	End	Letter
06/09/2021	05/02/2022	A
14/02/2022	09/07/2022	B
28/09/2020	06/02/2021	C
14/02/2021	09/07/2021	D

What i want is to show in my calendar table the corresponding letter, likely with the use of the related() function (this is a simplified example). How can i connect these two tables and make sure that the corresponding letter is shown for each date?

Thanks.

[MFelix]

Hi @cdcphist ,

In this case you want to show for the dates between September 6 2021 and February 5 2022 the letter A and for the February 14 2020 to July 9 2022 the letter B and so on you need to add a custom column with the following formula:

Letter =
MAXX (
    FILTER (
        Letters,
        'Calendar'[Date] >= Letters[Start]
            && 'Calendar'[Date] <= Letters[End]
    ),
    Letters[Letter]
)

[parry2k · Accepted solution]

@cdcphist add new column in calendar table using following DAX 

Letter = 
VAR __date = 'Calendar'[Date]
RETURN
CALCULATE ( MIN ( Letter[Letter] ), __date >= Letter[Start], __date <= Letter[End])

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡