Divide previous row by next row

[PrathSable]

Hi Guys,

I have the following data set:

Date	Keyword	Count
01-01-2020	ABCD	5
01-01-2020	DEFG	2
01-01-2020	HIGK	3
01-01-2020	LMNO	0
01-01-2020	PQRS	3
01-01-2020	TUVW	5
01-01-2020	XYZ	5
02-01-2020	ABCD	3
02-01-2020	DEFG	4
02-01-2020	HIGK	0
02-01-2020	LMNO	6
02-01-2020	PQRS	10
02-01-2020	TUVW	53
02-01-2020	XYZ	12

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

[speedramps]

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.

[nandukrishnavs]

@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 🙂

[amitchandak]

@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]

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

[harshnathani]

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]

@PrathSable 

Try this calculated column

Percentage = 
var nextDate=MAXX(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]*'Table'[Actual],BLANK())

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

[PrathSable]

@nandukrishnavs :

I think I confused you, I do not want the percentage.

 I just want to divide earlier record with latest one & then that would need to be multiplied with a corresponding value.

Want to achieve this??

Suggestions?

Regards,

PrathSable

[nandukrishnavs]

@PrathSable 

Okay Got it.

Val =
VAR nextDate =
    MAXX (
        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 ()
    ) * 'Table'[Actual]

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

[AlB]

@PrathSable 

Just change the order in the DIVIDE () then. You will have to multiply the result by the "actual" column if that is what you need

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 ( previousValue_, currentValue_ )

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

Cheers 

[PrathSable]

Hi @AlB ,

This works out , I guess there's something wrong in my data that I need to figure out.

However, is it possible to create a custom column for same period last year? something like this:

Dynamic for different Keywords, so that the aggregation remains perfect for me to calculate percentages of change.

FYI: There is data for multiple years, I tried sameperiodlastyear, previousyear; but doesnt work in calculated column 😞

Dynamic for different keywords for different dates

Appreciate your help in advance

Regards,

PrathSable

[AlB]

@PrathSable 

for last year's value:

Calculated Column =
VAR previousYearDate_ =
    DATE ( YEAR ( Table1[Date] ) - 1, MONTH ( Table1[Date] ), DAY ( Table1[Date] ) )
RETURN
    CALCULATE (
        DISTINCT ( Table1[Count] ),
        ALLEXCEPT ( Table1, Table1[Keyword] ),
        Table1[Date] = previousYearDate_
    )

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

Cheers 

[PrathSable]

Hi @AlB ,

Thanks for this. One last calculated column and a measure: Running total for dates for Individual keywords.

I tried creating Running total Quick measure but it doesn't work as desired as it does only dates, as I need to calculate a Running Total custom column or a measure that will bring into consideration the Keywords as well.

Like Running total for 2020 will be 2018+2019+2020

I used the below: 

Daily Search Volume running total in Date =

CALCULATE(

    SUM('Master Keywords and Search Volume'[Daily Search Volume]),

    FILTER(

        ALLSELECTED('Master Keywords and Search Volume'[Date]),

        ISONORAFTER('Master Keywords and Search Volume'[Date], MAX('Master Keywords and Search Volume'[Date]), DESC)

    )

)

Need to calculate Running Total

Truly appreciate your help on this.

Regards,

PrathSable

[harshnathani]

@PrathSable ,

You can create a calculated column.

Running Total = CALCULATE(SUM(Table8[Count]), FILTER(ALLEXCEPT(Table8,Table8[Keyword]), Table8[Date] <= EARLIER(Table8[Date])))

Regards,
Harsh Nathani

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

[PrathSable]

Hi All,

In continutation of the above calculation: I have another query which refers to the same. I need to derive a different kind of solution that needs to be worked upon:

E.g. below: I want to know whether this solution is achieveable through powerbi

Date	Keywords	Keyword Volume	Search Volume	Avg per month	Total records per month	Actual Search Volume	Week number
03-05-2018	ABCD	53	16600000	52.5483871	31	537345.00	18
03-05-2019	ABCD	54	16600000	54	31	5375234.00	18
03-05-2020	ABCD	91	16600000	90.64516129	31	537580.07	18

What I need to achive is:

Date	Keywords	Keyword Volume	Search Volume	Avg per month	Total records per month	Actual Search Volume	Week number
03-05-2018	ABCD	53	16600000	52.5483871	31	313096.09	18
03-05-2019	ABCD	54	16600000	54	31	319003.56	18
03-05-2020	ABCD	91	16600000	90.64516129	31	537580.07	18

The above table is derived from the below calculation: I need to consider max date (which would be every date for every Keyword

SO based on the Max Actual Search Volume: The Search volume will be Divided by the KeyWord of the same day: For. e.g.  03-05-2020: So 537580.07 / 91 = 5907.47. This value that is derived will then be Multiplied by the previous years Keyword to get the previous year's Actual search volume: So when we multiply 5907.47 to 54 which is the volume on 03-05-2018 ; I get the value as 319003.3. I need to achieve this in dynamically for all keywords.

Excel file is stored here: https://drive.google.com/file/d/1QZQzmyQmXSGpZF_R8MUsorrO36AIF8pT/view?usp=sharing 

But the only thing here is: The current year should have the actual value where-as the previous years will have the calculated value in a same column. Is this achieveable in PowerBI?

Regards,

PrathSable

[harshnathani]

Hi @PrathSable ,

Incase you want to use Calculated Columns.

Previous Date =

CALCULATE (

MAX ( 'Table'[Date] ), FILTER(

ALLEXCEPT ( 'Table','Table'[Keyword] ),

'Table'[Date] < EARLIER( ( 'Table'[Date] ))

)

)

Previous Value =

CALCULATE (

MAX('Table'[Count]) , FILTER(

ALLEXCEPT ( 'Table', 'Table'[Keyword] ),

'Table'[Date] = EARLIER('Table'[Previous Date])

))

Divide Val = DIVIDE ('Table'[Previous Value],'Table'[Count])

Final Val = 'Table'[Divide Val] * '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)

[AlB]

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]

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