Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Running Total restart each year

Hi I'm calculating a running total for each quarter each year and I would want it to stop and restart at the end of each year.  This is for TRACKED_LOANS. My current calculation is:         ...
  • ImkeF's avatar
    ImkeF
    6 years ago

    To use this Time Intelligence function you need a proper Date table and reference the column that has marked as the date column (not possible with a column like you showed in your picture).

     

    If you're not prepared to work with such a table, you'd have to add a year-filter into your original measure like so:

     

    TRACKED_LOANS running total in Year Quarter =
    CALCULATE(
        SUM('FACT_Unique Tracked Loans'[TRACKED_LOANS]),
        FILTER(
            ALLSELECTED('DIM_Date'[Year Quarter]),
            ISONORAFTER('DIM_Date'[Year Quarter], MAX('DIM_Date'[Year Quarter]), DESC)
        ),
        FILTER(
            ALL('DIM_Date'[Year]),
            'DIM_Date'[Year] = MAX('DIM_Date'[Year])
        )
    )