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

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

Running Total restart each year | Microsoft Fabric Community

9 Replies

ImkeF
Community Champion
6 years ago
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.
amitchandak
Super User
6 years ago
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
Anonymous
Not applicable
6 years ago

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
  Community Champion
  6 years ago
  The DATESYTD-function has to reference the DIM_Date and not the Fact-table.
Anonymous
Not applicable
6 years ago
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
  Community Champion
  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]) ) )
  - Anonymous
    Not applicable
    6 years ago
    Thank you so much this worked like a charm !
Anonymous
Not applicable
6 years ago
hey There

When I adda slicer of the quarter year this breaks the calculation and this gives me the tracked loans again
- ImkeF
  Community Champion
  6 years ago
  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]) ) )

Forum Discussion

Running Total restart each year