Running Total restart each year

[Anonymous]

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: 

 

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)

    )

)

[Anonymous]

hey There

When I adda slicer of the quarter year this breaks the calculation and this gives me the tracked loans again 

[ImkeF]

That's due to the ALLSELECTED you've used. I strongly recommend to work with a proper date table, then you can use standard solutions for your problems.

Now you can try sth like this:

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

[Anonymous]

Hello,

I just switched it to a date field in DIM_DATE and I'm still getting the same:

YTD SALES = CALCULATE(SUM('FACT_Unique Tracked Loans'[TRACKED_LOANS]), DATESYTD(DIM_Date[MonthName-Year]))

[ImkeF · Accepted solution]

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

[Anonymous]

Thank you so much this worked like a charm !

[Anonymous]

I tried this solution and I'm getting just the TRACKED_LOANS 

YTD SALES = CALCULATE(SUM('FACT_Unique Tracked Loans'[TRACKED_LOANS]), DATESYTD('FACT_Unique Tracked Loans'[Date]))

[ImkeF]

The DATESYTD-function has to reference the DIM_Date and not the Fact-table.

[amitchandak]

You can use datesytd and totalytd, It will restart after year

YTD Sales = CALCULATE(SUM('FACT_Unique Tracked Loans'[TRACKED_LOANS])),DATESYTD(('DIM_Date'[Date])))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[ImkeF]

Hi @Anonymous 

you could consider a more generich approach like so:

TRACKED_LOANS running total in Year Quarter =
CALCULATE(
    SUM('FACT_Unique Tracked Loans'[TRACKED_LOANS]),
    DATESYTD('DIM_Date'[Date]),
    )
)

You might have to replace the "Date" by the name of the date-column in your DIM_Date.