Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
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 🙂
@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))
Hi @amitchandak : This is close: But what I want to actually achieve is:
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
Hi @PrathSable ,
You cna use this measure
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
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 🙂
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.
Suggestions?
Regards,
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 🙂
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
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 😞
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:
Truly appreciate your help on this.
Regards,
PrathSable
You can create a calculated column.
Regards,
Harsh Nathani
Did 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
Hi @PrathSable ,
Incase you want to use Calculated Columns.
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |