Forum Discussion

homia1kr's avatar
homia1kr
Frequent Visitor
1 year ago
Solved

Dynamic Year End Calculation

Hello!

I have a date table and a table that has a breakdown of # of clients by month end.  I want to be able to pull the sum of the previous year-end number of clients based on the date selected in the slicer.  

 

I have this formula that works currently, but I'd like to make it dynamic so that I can look at this coming year and last year, rather than having the date hard-coded which means that I can only look at one year in my slicer.

 

Clients YE 23 = CALCULATE(SUM(Client_Table [SUM (Clients)]),

FILTER(
ALL('Date'[Month end]),
'Date'[Month end] = DATE(2023,12,31)
))

  • DataInsights's avatar
    DataInsights
    1 year ago

    homia1kr,

     

    Try this measure:

     

    Clients Previous YE =
VAR vSelectedYear =
    YEAR ( MAXX ( ALLSELECTED ( 'Date' ), 'Date'[Date] ) )
VAR vResult =
    CALCULATE (
        SUM ( Client_Table[SUM (Clients)] ),
        'Date'[Month End]
            = DATE ( vSelectedYear - 1, 12, 31 )
    )
RETURN
    vResult

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    1 year ago

    homia1kr,

     

    Try this measure:

     

    Clients Previous YE =
VAR vSelectedYear =
    YEAR ( MAXX ( ALLSELECTED ( 'Date' ), 'Date'[Date] ) )
VAR vResult =
    CALCULATE (
        SUM ( Client_Table[SUM (Clients)] ),
        'Date'[Month End]
            = DATE ( vSelectedYear - 1, 12, 31 )
    )
RETURN
    vResult

     

    • homia1kr's avatar
      homia1kr
      Frequent Visitor
      1 year ago

      Thank you SO much! I've been trying everything for the last 2 days. Works perfectly.