Last 2 year amount

[Narender]

Hi All,

I want a sum(amount) of last 2 year.Means sum(amount) group by last 2 year.

Like i want last 2 year sum.

Year   amount

2017   100$

2018   200$

-----------------

TOTAL  300$

Thanks

Narender

[v-jiascu-msft]

Hi Narender,

We can get the total in the "Total" row of the visual with the formula below. What's your issue?

Sales = sum('table'[Amount])

Best Regards,

Dale

[Narender]

Hi Dale, 

your expression will show the total amount. if i apply year in dimention in chart then it will show year wise amount.

I have year starting from 2000 to 2018 . So I want only last 2 year amount.

Means 2017 & 2018 amount only.

Year     Amount

2017    10$

2018    20$

Total    30$

So when 2019 will come then

It will show data for 2018 & 2019 only.

Thanks,

Narender

[pawel1]

what about the DATESINPERIOD function?

last 2 years of sales =
CALCULATE([Total Sales],
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]),-2,YEAR))

[Narender]

Hi,

As per your example , your are showing 4 year amount not 2 year.

If possible, Can you share a sample for this?

Thanks,

Narender

[pawel1]

'the last 2 years' measure calculates value for 2 years.  for example, 2017 is the sum of 2016-2017

[Narender]

Yes Pawel , But I want it group by year.

2016 and 2017  seperatly amount in same table.

Thanks,

Narender

[Narender]

Actually i want last 2 year sum of amount group by last 2 year.

year     amount

2017   10$

2018    20$

Total     30$

Thanks,

Narender

[v-jiascu-msft]

Hi Narender,

Please check out the demo in the attachment.

Measure =
IF (
    HASONEFILTER ( 'Table'[Date].[Year] ),
    IF (
        YEAR ( MIN ( 'Table'[Date] ) )
            >= YEAR ( TODAY () ) - 1,
        SUM ( 'Table'[Value] ),
        BLANK ()
    ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', YEAR ( 'Table'[Date] ) >= YEAR ( TODAY () ) - 1 )
    )
)

Best Regards,

Dale

[Narender]

Hi Dale,

it is showing me error in this line.

if(HASONEFILTER(TAX_TRANSACTION[ENTER_DATE].[Year])

.[Year] is not working with enter_date of tax_transactuion table.

Error: Column reference to 'ENTER_DATE' in table 'TAX_TRANSACTION' cannot be used with a variation 'Year' because it does not have any.

Please let me know how to resolve it.

Thanks,

Narender

[v-jiascu-msft]

Hi @Narender,

Can you share a dummy sample?

The cause could be that the column [ENTER_DATE] has a relationship with other table.

Best Regards,

Dale

[Narender]

Yes Dale,

Enter_date has a relationship with the date field of Calender.

Enter_Date  <--------------------  Date 

Let me know how to relove it?

Thanks,

Narender

[v-jiascu-msft · Accepted solution]

Hi Narender,

Try to use [Date] instead of [Enter_Date], please.

Best Regards,

Dale

[Narender]

Thank you Dale . Its working now. 

I need to add 1 more condition in it.

Last 2 year amount for the current month.

Example:

  Year                      Amount

 2017(June)              100$ (June2017 Amount)

 2018(June)              200$ (June2018 Amount)

Narender

[v-jiascu-msft]

Hi Narender,

It could be like below.

Measure =
IF (
    HASONEFILTER ( 'Table'[Date].[Year] ),
    IF (
        YEAR ( MIN ( 'Table'[Date] ) )
            >= YEAR ( TODAY () ) - 1,
        SUM ( 'Table'[Value] ),
        BLANK ()
    ),
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', YEAR ( 'Table'[Date] ) >= YEAR ( TODAY () ) - 1 ),
        FILTER ( 'tax_payer', 'tax_payer'[FlAG] = "A" )
    )
)

Best Regards,

Dale

[Narender]

Thank you very much Dale. 

By mistake I have edited my last question and ask for new 1 condition.

Here I am writing again.

Again i am writing both new conditions.

1) Add a Filter in same condition (I got the idea from your last reply)

2) New question: add current month filter in DAX with same above condition.

Year          Amount

2018        100$                              // Amount of June 2018

2017        200$                               // Amount of June 2017

Thank You,

Narender

[v-jiascu-msft]

Hi Narender,

I would suggest you open a new thread in this forum to discuss the new topic which is different from this topic. Thanks for your understanding in advanced.

Best Regards,

Dale

[Narender]

Hi Dale,

As you suggested , i have created a new thread for the  "Last 2 year Collection for a particular month".

Can you assist me for that as previous one question was similar to this one.

I have created new thread with the name  "Last 2 year Collection for a particular month".

Thanks,

Narender

[Narender]

Ok  Dale. 

Thanks,

Narender

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490