How to calculate relative change in comparison to prior month

[Anonymous]

Hello,

I have a table called "Searchterms_View" which contains all search queries that were entered on our website. 

The table contains a row for each search, for simplicity reasons lets say it only contains two columns the sessionId which is a unique identifier for every session and the searchQuery which contains the term the user typed.

Through the sessionId I can match each search to Date which lies in Dimension_View. 

I now want to determine how large the share of each searchterm is in the last 30 days and how that changed in relation to the prior 30 days.

So let's say 4/20 searches in the last 30 days were "battery" then that would amount to 20% in that month. But maybe the 30 days prior that figure was (15/100) 15%, then I would want the outcome to be +33%.

So far I have gotten this:

VAR last_30 =

CALCULATE(COUNT(Searchterms_View[searchQuery]), DATESINPERIOD(Dimension_View[date], LASTDATE(Dimension_View[date]), -30, DAY))

VAR prior_30 = CALCULATE(COUNT(Searchterms_View[searchQuery]), DATESINPERIOD(Dimension_View[date], LASTDATE(Dimension_View[date])-30, -60, DAY))

RETURN last_30/prior_30 - 1

This gets me how the absolute number has evolved, so the drop from 15 to only 4 would leave me with -73%, because the absolute number has decreased, meanwhile the relative share has increased. How would I do this? 

Cheers.

[amitchandak]

@Anonymous , I think this should be 30 only

VAR prior_30 = CALCULATE(COUNT(Searchterms_View[searchQuery]), DATESINPERIOD(Dimension_View[date], LASTDATE(Dimension_View[date])-30, -30, DAY))

[Anonymous]

You are right, that is not the core issue though. Check out the reply below, I generated some sample data which might help. 🙂 

This is what my current measure is getting for me, relative change between the absolute value, however I want the relative change between the share a searchterm made up in that month.

This is what I am currently getting, it displays the relative change between the two absolute values. However I want the relative change between the relative share.

The data is pasted seems to have been deleted, so I put it into a pastebin:

Searchterms_View:

https://pastebin.com/f94xknUf

Dimension_View:

https://pastebin.com/SCnVHc9g

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

Maybe you can try this code:

Measure = 
VAR _maxdate =
    CALCULATE( MAX( 'Dimension_View'[Date] ), REMOVEFILTERS(Searchterms_View[searchQuery] ))
VAR _last_30d = _maxdate - 30
VAR _last_60d = _last_30d - 30
VAR _last_30 =
    CALCULATE(
        COUNT( Searchterms_View[searchQuery] ),
        'Dimension_View'[Date] <= _maxdate,
        'Dimension_View'[Date] > _last_30d
    )
VAR _last_30_total =
    CALCULATE(
        COUNT( Searchterms_View[searchQuery] ),
        'Dimension_View'[Date] <= _maxdate,
        'Dimension_View'[Date] > _last_30d,
        REMOVEFILTERS( Searchterms_View[searchQuery] )
    )
VAR _last_60 =
    CALCULATE(
        COUNT( Searchterms_View[searchQuery] ),
        'Dimension_View'[Date] <= _last_30d,
        'Dimension_View'[Date] > _last_60d
    )
VAR _last_60_total =
    CALCULATE(
        COUNT( Searchterms_View[searchQuery] ),
        'Dimension_View'[Date] <= _last_30d,
        'Dimension_View'[Date] > _last_60d,
        REMOVEFILTERS( Searchterms_View[searchQuery] )
    )
RETURN
    DIVIDE(
        DIVIDE( _last_30, _last_30_total ),
        DIVIDE( _last_60, _last_60_total )
    )-1

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

[Anonymous]

Sir, I don't think you have any idea, how much I love you right now. If I could kiss you I would.

I have probably wasted 5-10 hours on this and we even had a Power BI consultant here who pointed me in a completely different direction and you have this very elegant solution without need for any new columns or even tables.


I am very, very grateful. Thank you so much.