Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Narender
Resolver I
Resolver I

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
Solution Sage
Solution Sage

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. 

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

 

 

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.

 

 

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

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.

 

 

 

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

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.

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

 

 

 

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

Hi ,

 

Please see the below screen shot.

 

1.jpg

You can see it is showing year 2015,2016,2017 data. But i want to see only last 2 year data means 2017 and 2018.

Actually i dont have 2018 data thats why it is showing upto 2017 year data not 2018 data.   

 

So how can we show last 2 year data not all year data?

 

 

Narender

 

 

If you want to exclude years from your graph you could add a sclicer for Years

Thank you.

 

But I dont want any hard code becuse i want to automatically change year according to last year.

 

 

 

Narender

Hello,

 

given the following table of anual amounts:

YearAmount
2014100
2015125
2016133
2017150
2018

175

 

Would be your expected result?

Yes.

But when 2019 year come then it will show from 2015 to 2019.

 

 

Thanks.

 

Narender

So you don't want to sum up the years?

 

If you want to exclude values older than 5 years you could try:

 

Last5YearsOnly=
IF(MAX(Date[Years]>=YEARS(TODAY())-5,[TotalSum],BLANK())

 So you could simply exclude all values older than actual year -5.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.