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

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

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
Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

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

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.