Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
22 | |
7 | |
6 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |