Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate difference in last 5 years data ...

hi all,

please help I want to get last 5 years data (before last or current year, 2018 in this case, not want to hard code it), last row of every year and calculate year-wise difference like here the difference of current year last row/transaction price and previous year last row/transaction price:

desired output:

 

Country Name   Date        Price   Difference
------------   ---------   -----   ----------
Argentina      7/29/2013    3.21        
Argentina      6/28/2014    3.31          0.1
Argentina      9/28/2015    3.44         0.13
Argentina      6/28/2016    3.55         0.11
Argentina      8/26/2017    3.66         0.11

Brazil         7/29/2013    2.21
Brazil         6/28/2014    2.31          0.1
Brazil         9/28/2015    2.44         0.13
Brazil         6/28/2016    2.55         0.11
Brazil         8/26/2017    2.66         0.11

 

in above desired output, first difference is null because 2012 is beyond the scope, not included in 5 years which are from 2013-2017 in this case ... on 2nd row difference (3.31 of 2014 - 3.21 of 2013) will show 0.1 and so on... 

 

Sample Data:

 

Country Name  Date         Price
------------  -----------  ------
Argentina     4/1/2012     3.10
Argentina     7/31/2012    3.11
Argentina     4/1/2013     3.20
Argentina     7/29/2013    3.21
Argentina     3/1/2014     3.30
Argentina     6/28/2014    3.31
Argentina     3/1/2015     3.40
Argentina     9/28/2015    3.44
Argentina     3/1/2016     3.50
Argentina     6/28/2016    3.55
Argentina     3/1/2017     3.60
Argentina     8/26/2017    3.66
Argentina     3/1/2018     3.70
Argentina     9/21/2018    3.77

Brazil        4/1/2012     2.10
Brazil        7/31/2012    2.11
Brazil        4/1/2013     2.20
Brazil        7/29/2013    2.21
Brazil        3/1/2014     2.30
Brazil        6/28/2014    2.31
Brazil        3/1/2015     2.40
Brazil        9/28/2015    2.44
Brazil        3/1/2016     2.50
Brazil        6/28/2016    2.55
Brazil        3/1/2017     2.60
Brazil        8/26/2017    2.66
Brazil        3/1/2018     2.70
Brazil        9/21/2018    2.77

 

hope all cleared.
regards

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    Hi Anonymous 

    Ok, I got it. Table above is the data you want to show and you've removed rows that are not the last data in each year. I just mistook the two tables were irrelevant😅.

    -

    create the measure and put it into the Filters on this visual :

    showlastrow_in_last5years =
VAR _lastyears = 5
VAR _maxdate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALL ( 'Table' ) )
VAR _endyear =
    YEAR ( _maxdate )
VAR _startyear =
    YEAR ( _maxdate ) - _lastyears
VAR _maxdate_eachyear =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[CountryName] = MAX ( 'Table'[CountryName] )
                && 'Table'[Date].[Year] = YEAR ( MAX ( 'Table'[Date] ) )
        )
    )
RETURN
    IF (
        YEAR ( MIN ( 'Table'[Date] ) ) >= _startyear
            && YEAR ( MIN ( 'Table'[Date] ) ) <= _endyear,
        IF ( MAX ( 'Table'[Date] ) = _maxdate_eachyear, 1, 0 ),
        0
    )

    then, create the measure and put it into the visual,

    Difference =
VAR _currPrice =
    MAX ( 'Table'[Price] )
VAR _lastPrice =
    CALCULATE (
        MAX ( 'Table'[Price] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] < MAX ( 'Table'[Date] )
                && 'Table'[CountryName] = MAX ( 'Table'[CountryName] )
        )
    )
RETURN
    IF ( ISBLANK ( _lastPrice ), BLANK (), _currPrice - _lastPrice )

    -

    before:

    after:

    See sample file attached bellow.

     

    Best Regards,

    Community Support Team _ Tang

    If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.

  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    Hi Anonymous 

    The difference is the value of column Price is not incremental. so I need to get the previous Price according to previous date, instead of get max Price when date < current date.

    so change the measure like bellow,

    -

    Difference1 =
VAR _currPrice =
    MAX ( 'Table'[Price] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] < MAX ( 'Table'[Date] )
                && 'Table'[CountryName] = MAX ( 'Table'[CountryName] )
        )
    )
VAR _lastPrice =
    CALCULATE (
        MAX ( 'Table'[Price] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] = _predate
                && 'Table'[CountryName] = MAX ( 'Table'[CountryName] )
        )
    )
RETURN
    IF ( ISBLANK ( _lastPrice ), BLANK (), _currPrice - _lastPrice )

     result:

    -

    by the way, if you want to filter year 2018 out from the visual, 

     

    Best Regards,

    Community Support Team _ Tang

    If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.

     

7 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    5 years ago

    Hi Anonymous 

    Ok, I got it. Table above is the data you want to show and you've removed rows that are not the last data in each year. I just mistook the two tables were irrelevant😅.

    -

    create the measure and put it into the Filters on this visual :

    showlastrow_in_last5years =
VAR _lastyears = 5
VAR _maxdate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALL ( 'Table' ) )
VAR _endyear =
    YEAR ( _maxdate )
VAR _startyear =
    YEAR ( _maxdate ) - _lastyears
VAR _maxdate_eachyear =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[CountryName] = MAX ( 'Table'[CountryName] )
                && 'Table'[Date].[Year] = YEAR ( MAX ( 'Table'[Date] ) )
        )
    )
RETURN
    IF (
        YEAR ( MIN ( 'Table'[Date] ) ) >= _startyear
            && YEAR ( MIN ( 'Table'[Date] ) ) <= _endyear,
        IF ( MAX ( 'Table'[Date] ) = _maxdate_eachyear, 1, 0 ),
        0
    )

    then, create the measure and put it into the visual,

    Difference =
VAR _currPrice =
    MAX ( 'Table'[Price] )
VAR _lastPrice =
    CALCULATE (
        MAX ( 'Table'[Price] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] < MAX ( 'Table'[Date] )
                && 'Table'[CountryName] = MAX ( 'Table'[CountryName] )
        )
    )
RETURN
    IF ( ISBLANK ( _lastPrice ), BLANK (), _currPrice - _lastPrice )

    -

    before:

    after:

    See sample file attached bellow.

     

    Best Regards,

    Community Support Team _ Tang

    If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      thanks a lot v-xiaotang for your interest to help. this is great... 👍

      best regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      hi v-xiaotang 

      implemented both measures as instructed, 5 year last day data is working fine ( the filter ).

      the difference is seems calculating wrong but right for first one only can be seen in screenshot below where i have showed the both current and previous prices side by side in Excel and used formula " current - previous " and results not matched or something i missing or misunderstood? please check and guide.

      check below in red rectangle for Argentina.

      regards

      • v-xiaotang's avatar
        v-xiaotang
        Community Support
        5 years ago

        Hi Anonymous 

        The difference is the value of column Price is not incremental. so I need to get the previous Price according to previous date, instead of get max Price when date < current date.

        so change the measure like bellow,

        -

        Difference1 =
VAR _currPrice =
    MAX ( 'Table'[Price] )
VAR _predate =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] < MAX ( 'Table'[Date] )
                && 'Table'[CountryName] = MAX ( 'Table'[CountryName] )
        )
    )
VAR _lastPrice =
    CALCULATE (
        MAX ( 'Table'[Price] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] = _predate
                && 'Table'[CountryName] = MAX ( 'Table'[CountryName] )
        )
    )
RETURN
    IF ( ISBLANK ( _lastPrice ), BLANK (), _currPrice - _lastPrice )

         result:

        -

        by the way, if you want to filter year 2018 out from the visual, 

         

        Best Regards,

        Community Support Team _ Tang

        If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    please help as i searched but majority of solutions are just sum of total sales previous year and total sales of current year but not as specific data as in my case as in first step (as per my understanding) it should have every years last transaction of 5 years and then calculate them.

    regards

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    5 years ago

    Hi Anonymous 

    thank you for your detailed discription, but  I still need to check with you.

    -

    do you mean you want to set a filter to get last 5 years data selected, and then get the diff between previous year last price and current year last price in each row according to column Country Name? but if there are different dates in same year, how to calculate? could you mark the result in sample data bellow?

     

    Country Name  Date         Price    Difference
------------  -----------  ------   -----------
Argentina     4/1/2012     3.10         
Argentina     7/31/2012    3.11         
Argentina     4/1/2013     3.20
Argentina     7/29/2013    3.21
Argentina     3/1/2014     3.30
Argentina     6/28/2014    3.31
Argentina     3/1/2015     3.40
Argentina     9/28/2015    3.44

     

    Best Regards,

    Community Support Team _ Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      v-xiaotang thank you so much for the reply.

      1) do you mean you want to calculate difference each row according to column Country Name?

      yes, also included desired output in my opening post here, i think this will be self explanatory.

       

      2) how to calculate?

      also it included in my desired output ( current - previous ) which contains data Country wise + Last Date data of Every Year not matter when it was done.

      say, if a product purchased by company last time in previous year, date was 18-Nov-2012 and in 2013 it was last time purchased on 28-Jun-2013, (no matter how many times it was purchased during both years), the concern is to get only last transactions of both years to calculate the difference. 

      hope that clear.

      regards