Advance your Data & AI career with 50 days of live learning, dataviz contests, hands-on challenges, study groups & certifications and more!
Get registeredGet Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now
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:
| DateEvent1 | DateEvent2 | YearEvent1 | YearEvent2 |
| 6/18/2020 | 1/11/2023 | 2020 | 2023 |
| 11/27/2022 | 2022 | ||
| 7/30/2020 | 2/19/2021 | 2020 | 2021 |
| 6/18/2021 | 11/27/2022 | 2021 | 2022 |
| 7/2/2021 | 12/18/2020 | 2021 | 2020 |
| 1/29/2021 | 2021 | ||
| 8/6/2021 | 2/19/2023 | 2021 | 2023 |
| 8/12/2021 | 2021 | ||
| 5/26/2022 | 2022 | ||
| 10/8/2021 | 5/26/2021 | 2021 | 2021 |
| 4/12/2022 | 2022 | ||
| 6/25/2022 | 12/18/2020 | 2022 | 2020 |
| 7/20/2022 | 1/29/2021 | 2022 | 2021 |
| 8/12/2022 | 2022 | ||
| 4/21/2021 | 2021 | ||
| 8/20/2022 | 5/25/2022 | 2022 | 2022 |
| 8/27/2022 | 5/26/2020 | 2022 | 2020 |
| 1/18/2023 | 5/26/2021 | 2023 | 2021 |
| 2/8/2023 | 2023 | ||
| 3/10/2023 | 5/26/2021 | 2023 | 2021 |
| 4/13/2023 | 2/21/2023 | 2023 | 2023 |
| 2/19/2023 | 2023 | ||
| 4/30/2023 | 2023 | ||
| 5/26/2021 | 2021 |
#Event1 = COUNT('Table'[DateEvent1])
#Event2 = COUNT('Table'[DateEvent2])
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
But when I select 2023 in Event2 Year, I get the correct number of Event2-s, but only 3 Event1-s in 2023.
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?
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
Check out the October 2025 Power BI update to learn about new features.
| User | Count |
|---|---|
| 8 | |
| 6 | |
| 5 | |
| 5 | |
| 4 |
| User | Count |
|---|---|
| 25 | |
| 16 | |
| 8 | |
| 7 | |
| 7 |