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 ...
  • 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

     

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.