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: 

 

 

 

 

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)
    )
)
Need Help
  • 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])
    )
)

     

9 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity 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's avatar
    amitchandak
    Icon for Super User rankSuper 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's avatar
    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's avatar
      ImkeF
      Icon for Community Champion rankCommunity Champion
      6 years ago

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

       

  • Anonymous's avatar
    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's avatar
      ImkeF
      Icon for Community Champion rankCommunity 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's avatar
        Anonymous
        Not applicable
        6 years ago

        Thank you so much this worked like a charm !

  • Anonymous's avatar
    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's avatar
      ImkeF
      Icon for Community Champion rankCommunity 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])
    )
)