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
Highlighted
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.

 

 

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
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,337)