cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver I

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

2 ACCEPTED SOLUTIONS
Employee

Hi @Narender,

```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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Resolver I

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

Narender

4 REPLIES 4
Employee

Hi @Narender,

```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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Resolver I

Thanks,

Narender

Resolver I

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

Narender

Resolver I

HI Yuliana,

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

Announcements

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors