Last 2 year Collection for a particular month

[Narender]

Hi All,

I want to get a amount of last 2 year of current month.

Means Sum(amount) where year = Last 2 year and month = Current month.

Example:

Year          Amount

2018        100$                              // Amount of June 2018

2017        200$                               // Amount of June 2017

Thank You,

Narender

[v-shex-msft]

HI @Narender,

You can use following measure to achieve your goal:

Measure = 
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Date].[Year]
            >= MAX ( 'Table'[Date].[Year] ) - 1
            && 'Table'[Date].[Year] <= MAX ( 'Table'[Date].[Year] )
    ),
    VALUES ( 'Table'[Date].[Month] )
)

Regards,

Xiaoxin Sheng

[Narender]

Tyanks Sheng.

But i dont need each month amount.

I need current month amount like month(today()) of last year (2017) and month(today()) of this year (2018).

So amount will change according to current month of current year and current month of last year.

like 

year          measure

2017          100$                                      // This is current month(June) amount of previous year(2017)

2018           200$                                     // This is current month(June) amount of current year (2018)

Thanks,

Narender

[v-shex-msft · Accepted solution]

Hi @Narender,

I modify my formula and current it only return this year and previous year records:

measure =
VAR _currentDate =
    MAX ( 'Table'[Date] )
RETURN
    IF (
        YEAR ( _currentDate )
            IN { YEAR ( TODAY () ) - 1, YEAR ( TODAY () ) },
        CALCULATE (
            SUM ( 'Table'[Amount] ),
            VALUES ( 'Table'[Date].[Year] ),
            'Table'[Date].[monthNo] = MONTH ( TODAY () )
        )
    )

Regards,

Xiaoxin Sheng

[Narender]

Hi Xiaoxin ,

Thanks for your reply.

Your mean to say

"This Year current month and Previous year Current month records" ?

Thanks,

Narender

[Ashish_Mathur]

Hi @Narender,

Sharre the link from where i can download your PBI/Excel file.  Also, show the expected result there

[v-shex-msft]

Hi @Narender,

Yes, current it only return current year and previous year based on today function.

Regards,
Xiaoxin Sheng