Forum Discussion

JL2007's avatar
JL2007
Frequent Visitor
3 years ago
Solved

Rolling 3 Months not Filtering

Hi, I am trying to get a rolling total of $ Values over the previous 3 months, I've been able to get this to work with the code below,  but my problem now is when trying to filter for different offices, it will not filter. It just stays the total rolling 3 months. Is there something wrong with the code?

 

IF (SUM('4 Year Bookings'[Bookings]) = 0,
    BLANK(),
CALCULATE (
    SUM ( '4 Year Bookings'[Bookings] ),
    ALL ( '4 Year Bookings'),
    DATESINPERIOD('4 Year Bookings'[Date Order],MAX('4 Year Bookings'[Date Order]),-3,MONTH)))
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    try use all+values

    ALL ( '4 Year Bookings'),

    VALUES('4 Year Bookings'[Office],

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    because filters are ignored by ALL ( '4 Year Bookings'), try to leave it out

    • JL2007's avatar
      JL2007
      Frequent Visitor
      3 years ago

      I did try that, and then the rolling 3 month total that does work stops working. The only way I've been able to succesfully calculate the last 3 month values is by  having the 'ALL' option, but then it breaks the filter.

      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User
        3 years ago

        try use all+values

        ALL ( '4 Year Bookings'),

        VALUES('4 Year Bookings'[Office],