Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
hello everyone i have a normal calendar table and another table called selection table
i made a measure
Filter Latest Month For SP =
SWITCH(
SELECTEDVALUE('Period Select'[ID]),
1, IF( AND(MAX('Calendar'[Week Number]) = WEEKNUM(TODAY(),2),MAX('Calendar'[Year Number]) = YEAR(TODAY())),1,0),
2, IF( AND(MAX('Calendar'[Month Number]) = MONTH(TODAY()),MAX('Calendar'[Year Number]) = YEAR(TODAY())),1,0),
3, IF( AND(MAX('Calendar'[Quarter Number]) = QUARTER(TODAY()),MAX('Calendar'[Year Number]) = YEAR(TODAY())),1,0),
4, IF(MAX('Calendar'[Year Number]) = YEAR(TODAY()) , 1 , 0),
5, IF(MAX('Calendar'[Year Number]) = YEAR(TODAY()) -1 , 1 , 0),
6,1
)
i added this measure to my 2 slicers , year slicer and timeline slicer , and set it to is 1
when i select the main slicer selection it works
Examples:
but it doesnt affect my measures or does a filter to my visuals
it should say from june 19 to june 23 but it shows me my whole data
Solved! Go to Solution.
ok i just created a new table
Test Dynamic Table =
UNION(
SELECTCOLUMNS(
FILTER('Calendar', 'Calendar'[Date] >= TODAY() - WEEKDAY(TODAY(), 2) + 1 && 'Calendar'[Date] <= TODAY()),
"Date", 'Calendar'[Date],
"Type", "Current Week",
"Order", 1
),
SELECTCOLUMNS(
FILTER('Calendar', 'Calendar'[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) && 'Calendar'[Date] <= TODAY()),
"Date", 'Calendar'[Date],
"Type", "Current Month",
"Order", 2
),
SELECTCOLUMNS(
FILTER('Calendar', 'Calendar'[Date] >= DATE(YEAR(TODAY()), INT((MONTH(TODAY()) - 1) / 3) * 3 + 1, 1) && 'Calendar'[Date] <= TODAY()),
"Date", 'Calendar'[Date],
"Type", "Current Quarter",
"Order", 3
),
SELECTCOLUMNS(
FILTER('Calendar', 'Calendar'[Date] >= DATE(YEAR(TODAY()), 1, 1) && 'Calendar'[Date] <= TODAY()),
"Date", 'Calendar'[Date],
"Type", "Current Year",
"Order", 4
),
SELECTCOLUMNS(
FILTER('Calendar', 'Calendar'[Date] >= DATE(YEAR(TODAY()) - 1, 1, 1) && 'Calendar'[Date] <= DATE(YEAR(TODAY()) - 1, 12, 31)),
"Date", 'Calendar'[Date],
"Type", "Last Year",
"Order", 5
),
SELECTCOLUMNS(
FILTER('Calendar', 'Calendar'[Date] >= MIN('Calendar'[Date]) && 'Calendar'[Date] <= MAX('Calendar'[Date])),
"Date", 'Calendar'[Date],
"Type", "Custom",
"Order", 6
)
)
made a relationship between my new table and calendar table from "Date" Column and szet it to both and it worked
ok i just created a new table
Test Dynamic Table =
UNION(
SELECTCOLUMNS(
FILTER('Calendar', 'Calendar'[Date] >= TODAY() - WEEKDAY(TODAY(), 2) + 1 && 'Calendar'[Date] <= TODAY()),
"Date", 'Calendar'[Date],
"Type", "Current Week",
"Order", 1
),
SELECTCOLUMNS(
FILTER('Calendar', 'Calendar'[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) && 'Calendar'[Date] <= TODAY()),
"Date", 'Calendar'[Date],
"Type", "Current Month",
"Order", 2
),
SELECTCOLUMNS(
FILTER('Calendar', 'Calendar'[Date] >= DATE(YEAR(TODAY()), INT((MONTH(TODAY()) - 1) / 3) * 3 + 1, 1) && 'Calendar'[Date] <= TODAY()),
"Date", 'Calendar'[Date],
"Type", "Current Quarter",
"Order", 3
),
SELECTCOLUMNS(
FILTER('Calendar', 'Calendar'[Date] >= DATE(YEAR(TODAY()), 1, 1) && 'Calendar'[Date] <= TODAY()),
"Date", 'Calendar'[Date],
"Type", "Current Year",
"Order", 4
),
SELECTCOLUMNS(
FILTER('Calendar', 'Calendar'[Date] >= DATE(YEAR(TODAY()) - 1, 1, 1) && 'Calendar'[Date] <= DATE(YEAR(TODAY()) - 1, 12, 31)),
"Date", 'Calendar'[Date],
"Type", "Last Year",
"Order", 5
),
SELECTCOLUMNS(
FILTER('Calendar', 'Calendar'[Date] >= MIN('Calendar'[Date]) && 'Calendar'[Date] <= MAX('Calendar'[Date])),
"Date", 'Calendar'[Date],
"Type", "Custom",
"Order", 6
)
)
made a relationship between my new table and calendar table from "Date" Column and szet it to both and it worked
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |