Forum Discussion

PrathSable's avatar
PrathSable
Icon for Advocate II rankAdvocate II
6 years ago

Divide previous row by next row

Hi Guys,

I have the following data set:

 

DateKeywordCount
01-01-2020ABCD5
01-01-2020DEFG2
01-01-2020HIGK3
01-01-2020LMNO0
01-01-2020PQRS3
01-01-2020TUVW5
01-01-2020XYZ5
02-01-2020ABCD3
02-01-2020DEFG4
02-01-2020HIGK0
02-01-2020LMNO6
02-01-2020PQRS10
02-01-2020TUVW53
02-01-2020XYZ12

 

For every Keyword, I need to divide the Keyword previous date by the next keyword date to get the % of increase. This has to be dynamic. For e.g. for Keyword ABCD, I would want to divide 5 which is the count which is for 01-01-2020 BY  3 which is the count of 02-02-2020. SO it will be 5/3 = 67.667%

 

I tried working on it, but no success. I don't want to do this calculation in excel is it will be updating the excel file everytime the new data comes in, so is there a way to achive it through a custom column & not be a measure. Because I would then want to multiply this value with a new individual record to get the individual calculation as I have a huge amount of data around (1M)

 

Any help on this is truly appreciated...

 

Regards,

PrathSable

 

Need Help

17 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi PrathSable

    Create a calculated column in your table

    Calc Column =
VAR previousDate_ =
    CALCULATE (
        MAX ( Table1[Date] ),
        ALLEXCEPT ( Table1, Table1[Keyword] ),
        Table1[Date] < EARLIER ( Table1[Date] )
    )
VAR previousValue_ =
    CALCULATE (
        DISTINCT ( Table1[Count] ),
        ALLEXCEPT ( Table1, Table1[Keyword] ),
        Table1[Date] = previousDate_
    )
VAR currentValue_ = Table1[Count]
RETURN
    DIVIDE ( currentValue_, previousValue_ )

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

    • PrathSable's avatar
      PrathSable
      Icon for Advocate II rankAdvocate II
      6 years ago

      Hi AlB ,

       

      Not sure, what I am doing wrong: I used the same calculation you provided but here is the output in Yellow that I am getting on my actual file:

       

       

      Any suggestions?

       

      Regards,

      PrathSable

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    PrathSable , Try as new column


    Last Date = maxx(filter(table,[date]<earlier([date]) && [keyword] =earlier([keyword])),[date])
    Ration with Last = divide([Count],maxx(filter(table,[date]=earlier([Last Date ]) && [keyword] =earlier([keyword])),[Count]))

     

    In a measure this how you get last value with help from date table


    Last Day Non Continous = CALCULATE(sum('Table'[Count]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))
    Day behind Sales = CALCULATE(SUM(Table[Count]),dateadd('Date'[Date],-1,Day))

    • PrathSable's avatar
      PrathSable
      Icon for Advocate II rankAdvocate II
      6 years ago

      Hi amitchandak : This is close: But what I want to actually achieve is:

       
       

      Actual values to find

      For our formula for 02-01-2020 we get the divide % for 01-02-2020. Any suggestions on how to achieve the above?

       

      Regards,

      PrathSable

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi PrathSable ,

         

        You cna use this measure

         

        Divided Value =
        VAR previousDate_ =
        CALCULATE (
        MAX ( 'Table'[Date] ), FILTER(
        ALLEXCEPT ( 'Table','Table'[Keyword] ),
        'Table'[Date] < MAX ( 'Table'[Date] ))
        )
        VAR previousValue_ =
        CALCULATE (
        MAX( 'Table'[Count] ), FILTER(
        ALLEXCEPT ( 'Table', 'Table'[Keyword] ),
        'Table'[Date] = previousDate_
        ))
        RETURN
        DIVIDE ( previousValue_, MAX('Table'[Count])) * MAX('Table'[Actual])
         
         
         

        Regards,
        Harsh Nathani

        Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

  • nandukrishnavs's avatar
    nandukrishnavs
    Icon for Community Champion rankCommunity Champion
    6 years ago

    PrathSable 

     

    Your example is a bit confusing. 5/3 =67.667?

     

    Try this 

    Percentage = 
var nextDate=MINX(FILTER(ALL('Table'),'Table'[Keyword]=EARLIER('Table'[Keyword])&&'Table'[Date]>EARLIER('Table'[Date])),'Table'[Date])
var nextDateValue= SUMX(FILTER(ALL('Table'),'Table'[Date]=nextDate&&'Table'[Keyword]=EARLIER('Table'[Keyword])),'Table'[Count])
return DIVIDE(nextDateValue,[Count],BLANK())

    You may have to tweak the logic based on your real scenario.



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

     

  • speedramps's avatar
    speedramps
    Icon for Super User rankSuper User
    6 years ago

    Hi Prath
    Please consider this solution

    Either by using Query Group By or DAX table functions do the following 

    Create a “now” subset of your original table with just the latest value per keyword.

    Create a “remainder” subset of your original table with all records except the records on the above subset.

    Create a “before”  subset of your “remainder”  table with just the latest value per keyword.

    You can now report “now” - “before” by keyword.