cancel
Showing results for
Did you mean:
Member

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

15 REPLIES 15
Established Member

## Re: Last Five year collection

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.

Member

## Re: Last Five year collection

Hello Floriankx,

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

Established Member

## Re: Last Five year collection

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.

Member

## Re: Last Five year collection

Thanks.

This is also not work

VAR latestdate = LASTDATE(TAX_TRANSACTION[ENTER_DATE])

and

VAR latestdate = YEAR(LASTDATE(TAX_TRANSACTION[ENTER_DATE]))

Becuase i am using 'Dates 5'[Year]>=latestdate-2)).

Dates 5'[Year] is the year of date5 table and TAX_TRANSACTION[ENTER_DATE] is the date of TAX_TRANSACTION table.

Also LASTDATE will take the date not the year if i am not wrong?

Narender

Established Member

## Re: Last Five year collection

Hello,

I forgot to wrap the LATESTDATE in YEAR, but you did it right intuitivly.

I assume your Date columns are related.

So you should use

`VAR LatestYear= YEAR(LASTDATE('Date 5'[Dates)).`

If you have a Year column in your DateTable you may use as well

`VAR LatestYear=MAX('Date 5'[Year]).`

Result should be the same.

Best regards.

Member

## Re: Last Five year collection

Hello,

I already tried with both options but didnt get the result as i want.

Last2Years =
VAR latestdate = MAX('Dates 5'[Year])
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]>=latestdate-2))

Actual amount of 2017 +2018 is 227473 which is showing under year 2018. I want this dividation into 2017 and 2018 year only.

Thanks,

Narender

Highlighted
Established Member

## Re: Last Five year collection

Hello,

for YearOnlyValues you can just use SUM:

CALCULATE (
SUM (TAX_TRANSACTION[TAX_SUB_TRANS.AMOUNT]),
FILTER ( ALL(TAX_TRANSACTION),TAX_TRANSACTION[TAX_SUB_TRANS.CHARGE_TYPE_NO]=2))

This should give you the Value for one year.

Member

## Re: Last Five year collection

This means, Power BI has no feature to calculate the amount with the range of year that i want.

Like i want last 2 year sum.

Sum(year) of last 2 year only

Year   amount

2017   100\$

2018   200\$

Thanks

Narender

Established Member

## Re: Last Five year collection

Maybe you can create a small sample with your expected result as table, so I may better understand your aim.

Announcements