cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Narender Member
Member

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

Accepted Solutions
Community Support Team
Community Support Team

Re: Last 2 year Collection for a particular month

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

6 REPLIES 6
Community Support Team
Community Support Team

Re: Last 2 year Collection for a particular month

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Narender Member
Member

Re: Last 2 year Collection for a particular month

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

 

 

Community Support Team
Community Support Team

Re: Last 2 year Collection for a particular month

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Narender Member
Member

Re: Last 2 year Collection for a particular month

Hi Xiaoxin ,

 

Thanks for your reply.

 

Your mean to say

 

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

 

Thanks,

 

Narender

Community Support Team
Community Support Team

Re: Last 2 year Collection for a particular month

Hi @Narender,

 

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

 

Regards,
Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
Super User
Super User

Re: Last 2 year Collection for a particular month

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/

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 323 members 2,645 guests
Please welcome our newest community members: