Forum Discussion

Narender's avatar
Narender
Icon for Resolver I rankResolver I
7 years ago
Solved

Last year collection amount on filter selection base

Hi All,

 

I want to calculate the amount of last year if there is no selection in year filter.So in this way it will show 2017 year amount.

But when I will select any other year like 2015 then it should calculate the 2014 year amount.

 

 

I am using this DAX expresion but it is not working as i want:

 

Measure 25 = IF(ISFILTERED(Dates[Year]),
CALCULATE(sum(TAX_TRANSACTION[TAX_SUB_TRANS.AMOUNT]),FILTER(Dates,YEAR(Dates[Date])=YEAR(TODAY())-1))
,
CALCULATE(sum(TAX_TRANSACTION[TAX_SUB_TRANS.AMOUNT]),FILTER(Dates,YEAR(Dates[Date])=YEAR(TODAY())-1)))

 

It is working if i dont select any year  in filter. But not working when i select the any year in year.

 

 

Please guide me to achieve the result.

 

Thanks,

 

Narender

 

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Narender,

     

    Please modify above formula to:

    Measure 25 =
IF (
    ISFILTERED ( Dates[Year] ),
    CALCULATE (
        SUM ( TAX_TRANSACTION[TAX_SUB_TRANS.AMOUNT] ),
        FILTER (
            ALL ( TAX_TRANSACTION ),
            YEAR ( TAX_TRANSACTION[Date] )
                = SELECTEDVALUE ( Dates[Year] ) - 1
        )
    ),
    CALCULATE (
        SUM ( TAX_TRANSACTION[TAX_SUB_TRANS.AMOUNT] ),
        FILTER ( Dates, YEAR ( Dates[Date] ) = YEAR ( TODAY () ) - 1 )
    )
)

    Best regards,

    Yuliana Gu

  • Narender's avatar
    Narender
    7 years ago

    I resolved it by adding month expression with the last year expression in DAX.

     

    Narender

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi Narender,

     

    Please modify above formula to:

    Measure 25 =
IF (
    ISFILTERED ( Dates[Year] ),
    CALCULATE (
        SUM ( TAX_TRANSACTION[TAX_SUB_TRANS.AMOUNT] ),
        FILTER (
            ALL ( TAX_TRANSACTION ),
            YEAR ( TAX_TRANSACTION[Date] )
                = SELECTEDVALUE ( Dates[Year] ) - 1
        )
    ),
    CALCULATE (
        SUM ( TAX_TRANSACTION[TAX_SUB_TRANS.AMOUNT] ),
        FILTER ( Dates, YEAR ( Dates[Date] ) = YEAR ( TODAY () ) - 1 )
    )
)

    Best regards,

    Yuliana Gu

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

      HI Yuliana,

       

      Thanks for your reply. Its is working now.

       

      I want a little modification in this.if possible please tell me below query dax expression.

       

      Actually i want  current month amount of last year when no filter is selected .  If user select filter year 2016 then it has to show 2015 year amount of current month.             

       

      Thanks,

       

      Narender

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

      Can you please guide me?

       

      Thanks,

       

      Narender

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

        I resolved it by adding month expression with the last year expression in DAX.

         

        Narender