Forum Discussion

Narender's avatar
Narender
Icon for Resolver I rankResolver I
8 years ago

Last Five year collection

Hi All,

 

I need to apply a condition in DAX in new measure under reports.

 I have a DAX expression as shown below.

 

CALCULATE (
SUM (TAX_TRANSACTION[TAX_SUB_TRANS.AMOUNT]),
FILTER ( ALL(TAX_TRANSACTION),TAX_TRANSACTION[TAX_SUB_TRANS.CHARGE_TYPE_NO]=2) AND TAX_TRANSATION(TAX_SUB_TRANS.[FLAG]) = 'A')

 

 

Now i need to add condition in above DAX expression"where year > year(today)-5"

 

means last 5 year amount.

 

Thanks,

 

Narender

Need Help

15 Replies

  • Floriankx's avatar
    Floriankx
    Icon for Solution Sage rankSolution Sage
    8 years ago

    Hello,

     

    TodayMinus5Years=CALCULATE([TotalSum],DATEISINPERIOD(TODAY(),-5,years))

    This gives you the value of the Last five years of the TODAY.

     

    What you describe is a little more like

    Last5Years=
    VAR ThisYear=YEAR(TODAY())
    CALCULATE([TotalSum],Filter(Tax_Transaction,YEAR(DateColumn)>=ThisYear-5)

    It is free scripted, so just give it a try and let us know. 

    • Narender's avatar
      Narender
      Icon for Resolver I rankResolver I
      8 years ago

      Hello Floriankx,

       

      Thanks for your reply.

      I used this DAX in new measure:

       

      Last2Years =
      VAR ThisYear = YEAR(TODAY())
      Return
      CALCULATE (
      SUM (TAX_TRANSACTION[TAX_SUB_TRANS.AMOUNT]),
      FILTER ( ALL(TAX_TRANSACTION),TAX_TRANSACTION[TAX_SUB_TRANS.CHARGE_TYPE_NO]=2),FILTER(ALL('Dates 5'),'Dates 5'[Year]>=ThisYear-2))
       

       

      I got the result that i want.But I am facing 1 issue.

       

      Its showing right total amount of last 2 year. But when i applied it under bar chart then it showed the total amount of last 2 year against each year of calender.

       

      See the below screen shot.

       

       

       

      I want only 2017 and 2018 year with actual amount of 2017 and 2018 not the  (2017+2018).

       

      Thanks,

       

      Narender

       

       

      • Floriankx's avatar
        Floriankx
        Icon for Solution Sage rankSolution Sage
        8 years ago

        Hello,

         

        Hello yes that's right because ThisYear always relates to TODAY().

         

        If you want to filter or group by year this doesn't work.

         

        Instead of VAR ThisYEAR=YEAR(TODAY()) you could try VAR LatestDate=LASTDATE([DateColumn])

        Then you also have to change ThisYear to LastestDate in your FILTER.

         

        Best regards.