Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Chris2016
Resolver I
Resolver I

Use Calendar Date to filter dates in various date columns

Hello,

Could someone please help with the following issue?
I have a table that calculates the number of different events by counting the event dates:

DateEvent1DateEvent2YearEvent1YearEvent2
6/18/20201/11/202320202023
 11/27/2022 2022
7/30/20202/19/202120202021
6/18/202111/27/202220212022
7/2/202112/18/202020212020
 1/29/2021 2021
8/6/20212/19/202320212023
8/12/2021 2021 
 5/26/2022 2022
10/8/20215/26/202120212021
4/12/2022 2022 
6/25/202212/18/202020222020
7/20/20221/29/202120222021
8/12/2022 2022 
 4/21/2021 2021
8/20/20225/25/202220222022
8/27/20225/26/202020222020
1/18/20235/26/202120232021
2/8/2023 2023 
3/10/20235/26/202120232021
4/13/20232/21/202320232023
 2/19/2023 2023
 4/30/2023 2023
 5/26/2021 2021

 

#Event1 = COUNT('Table'[DateEvent1])
 
#Event2 = COUNT('Table'[DateEvent2])
 
I have a calculated Calendar table.

Calendar = 
    VAR dates =
        CALENDAR ( DATE ( 2020, 1, 1 ),TODAY())
    VAR datetable =
        ADDCOLUMNS (
        dates,
        "Year", YEAR ( [Date] ),
        "Month", FORMAT ( [Date], "MMMM" ),
        "MonthNumber", MONTH ( [Date] ),
        "MonthNo", FORMAT( [Date], "MMM" ),
        "Year/Month", FORMAT ( [Date], "YYYY/MMMM" ),
        "YearMo", FORMAT ( [Date], "YYYYMM" ),
        "Fiscal Quarter", SWITCH( TRUE(),
               MONTH([Date]) = 1 || MONTH([Date]) = 2 || MONTH([Date]) = 3, "FQ3",
               MONTH([Date]) = 4 || MONTH([Date]) = 5 || MONTH([Date]) = 6, "FQ4",
               MONTH([Date]) = 7 || MONTH([Date]) = 8 || MONTH([Date]) = 9, "FQ1",
               MONTH([Date]) = 10 || MONTH([Date]) = 11 || MONTH([Date]) = 12, "FQ2"),
        "Fiscal Month", If(MONTH([Date])<7,MONTH([Date])+6,MONTH([Date])-6)
                  )
                 RETURN
                 datetable
There is an active relationship between Calendar Date > 'Table'[DateEvent1], and an inactive relationship between Calendar Date > 'Table'[DateEvent2].

I am trying to use a Calendar Year slicer so that, when selecting, for example, 2023, it renders the exact number of events that happened in that particular year, whether it was Event1 or Event2.
 
For now, if I select a slicer value based on the Event1 Year, I get, for example, 4 Event1-s in 2023:
Chris2016_0-1681922066549.png


But when I select 2023 in Event2 Year, I get the correct number of Event2-s, but only 3 Event1-s in 2023.

Chris2016_1-1681922280061.png

 

How can I use the Calendar table Year to render the overall numbers for the year 2023? I.e., in the above sample table, there were 4 Event1-s and 5 Event2-s in 2023. But, since the active relationship is between 'Calendar'[Date] and 'Table'[DateEvent1], the Calendar Year slicer is only rendering the truth for DateEvent1. Is there a way to bring the real numbers for both events?

Many thanks for any help!
0 REPLIES 0

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.