Forum Discussion
Calendar Table Not Filtering
NVM, found my issue. I realized I had placed Calculate as a VAR and just found that VAR are immutable so moved my Calculate after the Return and all is well. Thanks for your help.
Calculated tables do NOT refresh when slicers are changed. They will only change each time the whole report is refreshed using the Refresh button on desktop (or a refresh on PBI Service).
If you need to filter the start date of the table, I suggest that you create the table with the startDate hardcoded as DATE(2022,3,1), then create a measure to filter any visuals that use your calendar table.
e.g.
CalendarTableFilter = INT(
([MainFilterString]="Product A" && SELECTEDVALUE(Calendar[Date]) >= DATE(2022,3,1)) ||
(SELECTEDVALUE(Calendar[Date]) >= DATE(2023,1,1))
)
NVM, found my issue. I realized I had placed Calculate as a VAR and just found that VAR are immutable so moved my Calculate after the Return and all is well. Thanks for your help.
Okay this makes sense so I tried to do it through a measue but that is also not working. The issue is that I have a table of contracts with start and end dates and monthly amounts. I'm creating a report that showes expected forward revenue as a line and bars for actuals. The problem I'm having is that one contract type started nearly a year before the others, so I want my graph start date to change dynamically dependent if the user selects type A or all others. In my measure when I run the follwing, it caclulates monthly amounts correctly but will not change dynamically:
Expected Montly Rev =
VAR _minDate = MIN(tbl_Dates[MoYr])
VAR _maxDate = Max(tbl_Dates[MoYr])
VAR _Result = CALCULATE(
SUM(tbl_Contract[MonthlyPaymentAmount]),
FILTER(tbl_Contract, tbl_Contract[EndDate]>=_minDate && tbl_Contract[StartDate] <= _maxDate))
RETURN
_Result
When I alter it slightly to account for a dynamic filter on the start date, it completely breaks the sum and it doesn't filter dynamcially.Sum of Contract =
VAR _DateSel = IF([MainFilterString]="Product A",DATE(2022,3,1),DATE(2023,1,1))
VAR _minDate = CALCULATE(MIN(tbl_Dates[MoYr]), tbl_Dates[MoYr]>=_DateSel && tbl_Dates[MoYr]<DATE(2027,1,1))
VAR _maxDate = Max(tbl_Dates[MoYr])
VAR _Result = CALCULATE(
SUM(tbl_Contract[MonthlyPaymentAmount]),
FILTER(tbl_Contract, tbl_Contract[EndDate]>=_minDate && tbl_Contract[StartDate] <= _maxDate))
RETURN
_Result