Forum Discussion

Narender's avatar
Narender
Icon for Resolver I rankResolver I
8 years ago
Solved

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Narender
      Icon for Resolver I rankResolver I
      8 years ago

      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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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