year differences calculations

Question

Hello ,&nbsp;I have this challenge , I need to make calculations for three different columns&nbsp; &lt;2 years, 2-5 years and &gt;5 years using following measure below:&nbsp;&nbsp;EnhancLead = VAR SelectedYear = SELECTEDVALUE('Calendar'[Date].[Year])VAR FirstDayOfNextYear = DATE(SelectedYear + 1, 1, 1)RETURN&nbsp; &nbsp; COUNTROWS(&nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CecCaseDetail,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; VAR TopToStateID =&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECTEDVALUE(CecCaseEvent[ToStateOfProceedingID]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TOPN(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALL(CecCaseEvent),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CecCaseEvent[CecCaseID] = CecCaseDetail[CaseID] &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CecCaseEvent[ToStateOfProceedingID] &lt;&gt; CecCaseEvent[fromstateofproceedingid] &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CecCaseEvent[EventDate] &lt; FirstDayOfNextYear&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CecCaseEvent[EventOrder],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DESC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; RETURN&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; TopToStateID IN {4} &amp;&amp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COUNTROWS(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'CecLeadingCase',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'CecLeadingCase'[CecCaseId] = CecCaseDetail[CaseID]))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) &nbsp;

vijayp · Answer

Anonymous&nbsp; you can get these results&nbsp; using DATEADD function, try that!&nbsp;
Link to DateAdd function explanation -&nbsp;https://dax.guide/dateadd/

anonymous · Answer

Thank you for help but the link you attached I tried and did not get what I need can you help me to implement it in my measure?

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
Please create a new enter table and use [Period range] as the column in the matrix:
Table = 
DATATABLE(
    "Period range", STRING,
    "Order", INTEGER,
    {
        {"&lt;2 years", 1},
        {"2-5 years", 2},
        {"&gt;5 years", 3}
    }
)

Then use this measure as the value of the matrix.
EnhancLead0 =
VAR SelectedYear =
    YEAR ( TODAY () ) -- Replace 'Calendar'[Date] with TODAY() if it is relative to today's date.
VAR PrevYear = SelectedYear - 1
VAR FiveYear = SelectedYear - 4
VAR CurrPeriodRange =
    SELECTEDVALUE ( 'Table'[Order] )
VAR StartYear =
    SWITCH ( CurrPeriodRange, 1, PrevYear, 2, FiveYear, 3, 1900 )
VAR EndYear =
    SWITCH ( CurrPeriodRange, 1, SelectedYear, 2, PrevYear - 1, 3, FiveYear - 1 )
VAR Result =
    COUNTROWS (
        FILTER (
            CecCaseDetail,
            VAR TopToStateID =
                CALCULATE (
                    SELECTEDVALUE ( CecCaseEvent[ToStateOfProceedingID] ),
                    TOPN (
                        1,
                        FILTER (
                            ALL ( CecCaseEvent ),
                            CecCaseEvent[CecCaseID] = CecCaseDetail[CaseID]
                                &amp;&amp; CecCaseEvent[ToStateOfProceedingID] &lt;&gt; CecCaseEvent[fromstateofproceedingid]
                                &amp;&amp; RELATED ( 'Calendar'[Year] ) &gt;= StartYear
                                &amp;&amp; RELATED ( 'Calendar'[Year] ) &lt;= EndYear
                        ),
                        CecCaseEvent[EventOrder], DESC
                    )
                )
            RETURN
                TopToStateID
                    IN { 4 }
                        &amp;&amp; COUNTROWS (
                            FILTER ( 'CecLeadingCase', 'CecLeadingCase'[CecCaseId] = CecCaseDetail[CaseID] )
                        )
        )
    )
RETURN
    Result
&nbsp;
Best Regards,GaoCommunity Support Team
&nbsp;
If there is any post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;&nbsp;to help the other members find it more quickly.If I misunderstand your needs or you still have problems on it, please feel free to let us know.&nbsp;Thanks a lot!
How to get your questions answered quickly&nbsp;--&nbsp;&nbsp;How to provide sample data in the Power BI Forum

anonymous · Answer

Hello Gao,&nbsp;Thank you for response,I have question why did you change it (red cicle on the picture) should be there&nbsp;CecCaseEvent[EventDate]&nbsp;your solution calculates but deliver incorrect values. I believe it has something to do with variable for date and what I highlighted with red circle. Can you look at it again and double check?&nbsp;

Forum Discussion

year differences calculations

4 Replies

Related content

Calculate % difference between two years

Dynamic Calculation Different Period

Year over Year calculation

Different Calculations

Facing challenge to calculate previous year QTD