Forum Discussion

PowerBPainful's avatar
PowerBPainful
Frequent Visitor
2 years ago
Solved

Calendar Table Not Filtering

Hello,

 

I have a calendar table created using the following code:

tbl_MoDaYr =
    VAR  _DateSel = IF([MainFilterString]="Product A",DATE(2022,3,1),DATE(2023,1,1))
    VAR _Calen = CALENDAR(_DateSel,DATE(2026,12,31))
    RETURN
    _Calen
 
The problem I'm having is that it will always return the false of the IF as the date. I've checked the _DateSel and it returns the correct value based on the slicer selection. On a line graph the dates do not change, so I'm confused why the _DateSel is correct, but the calendar table does not change. I've also tried to use FILTER() on the _Calen using static values for start and end date of the _Calen. Can anyone help?
Need Help
  • PowerBPainful's avatar
    PowerBPainful
    2 years ago

    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.

3 Replies

  • vicky_'s avatar
    vicky_
    Icon for Super User rankSuper User
    2 years ago

    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))
    )

    • PowerBPainful's avatar
      PowerBPainful
      Frequent Visitor
      2 years ago

      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.

    • PowerBPainful's avatar
      PowerBPainful
      Frequent Visitor
      2 years ago

      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