cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Narender Member
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
Floriankx Established Member
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. 

Narender Member
Member

Re: Last Five year collection

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.

 

last2years.jpg 

 

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

 

Thanks,

 

Narender

 

 

Floriankx Established Member
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.

 

 

Highlighted
Narender Member
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?

 

Please suggest me .

 

 

Narender

Floriankx Established Member
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.

 

 

Narender Member
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))

 

last2years.jpglast2years1.jpg

 

 

 

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

Floriankx Established Member
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.

Narender Member
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

 

 

 

Floriankx Established Member
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.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 387 members 3,986 guests
Please welcome our newest community members: