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 2 year Collection for a particular month

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

 

1 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

6 REPLIES 6
v-shex-msft
Community Support
Community Support

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] )
)

29.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

 

 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi Xiaoxin ,

 

Thanks for your reply.

 

Your mean to say

 

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

 

Thanks,

 

Narender

Hi @Narender,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Narender,

 

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

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.