Forum Discussion

Alirezam's avatar
Alirezam
Helper V
7 years ago
Solved

Difference between two years

Hello,   I need to know how much the value changed on monthly basis. For example, if I select "Laundary", I like to view how much annual difference percentage was between 2017 and 2018. The questio...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi Alirezam ,

    For your requirement, we could create the measure below to achieve it.

    Measure =
VAR minyear =
    CALCULATE (
        SUM ( Table1[Sales] ),
        FILTER (
            'Table1',
            YEAR ( 'Table1'[Date] ) = YEAR ( MIN ( 'Table1'[Date] ) )
                && MONTH ( 'Table1'[Date] ) = MONTH ( MAX ( 'Table1'[Date] ) )
        )
    )
VAR maxyear =
    CALCULATE (
        SUM ( Table1[Sales] ),
        FILTER (
            'Table1',
            YEAR ( 'Table1'[Date] ) = YEAR ( MAX ( 'Table1'[Date] ) )
                && MONTH ( 'Table1'[Date] ) = MONTH ( MAX ( 'Table1'[Date] ) )
        )
    )
RETURN
    ( maxyear - minyear ) / maxyear

    Then we could create a line and clusterd column chart like below.

    More details, please refer to my attachment.

    Best  Regards,

    Cherry

     

v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

Hi Alirezam ,

For your requirement, we could create the measure below to achieve it.

Measure =
VAR minyear =
    CALCULATE (
        SUM ( Table1[Sales] ),
        FILTER (
            'Table1',
            YEAR ( 'Table1'[Date] ) = YEAR ( MIN ( 'Table1'[Date] ) )
                && MONTH ( 'Table1'[Date] ) = MONTH ( MAX ( 'Table1'[Date] ) )
        )
    )
VAR maxyear =
    CALCULATE (
        SUM ( Table1[Sales] ),
        FILTER (
            'Table1',
            YEAR ( 'Table1'[Date] ) = YEAR ( MAX ( 'Table1'[Date] ) )
                && MONTH ( 'Table1'[Date] ) = MONTH ( MAX ( 'Table1'[Date] ) )
        )
    )
RETURN
    ( maxyear - minyear ) / maxyear

Then we could create a line and clusterd column chart like below.

More details, please refer to my attachment.

Best  Regards,

Cherry

 

  • Alirezam's avatar
    Alirezam
    Helper V
    7 years ago

    Hi, Thanks for your reply. I did what you said but still the line does not show anyting and sticks to the x-axis. Please note that I get [date] from another table named 'Calendar'