Forum Discussion
Divide previous row by next row
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
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
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
You can create a calculated column.
Running Total = CALCULATE(SUM(Table8[Count]), FILTER(ALLEXCEPT(Table8,Table8[Keyword]), Table8[Date] <= EARLIER(Table8[Date])))Regards,
Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
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