Measures – Dynamic CAGR Calculation in DAX

9 Comments

1. I would like a line graph that shows the percentage change of the COUNT of employee ID’s in a column over a span of 16 quarters. I’m wondering if you could provide some assistance?

   • Ok, I have done some research on what you were asking. Maybe this will help.

     There is an assumption here that you have a list of employee IDs and Dates similar to the following table:
     Date | Employee ID
     1/1/2016 123
     1/1/2016 456
     1/2/2016 123
     1/2/2016 456
     1/3/2016 123
     1/4/2016 123
     1/4/2016 789

     Thus, the count for Badge 123 would be in total 4, it appeared every in every date.
     For badge 456 the count would be 2.

     This can be simply done with a measure something like:
     Count of Badges = Count( EmployeeID )
     Then in your visual you’d use the Employee ID and your new measure Count of Badges in a table
     This would yield:
     Employee ID | Count of Badges
     123 4
     456 2
     789 1

     When you mention % change it gets much more difficult. Now you have to determine how the percent change will operate.
     For our example you have a date range of 4 days. 1/1 to 1/4 for percent change you want to calculate the total of the first 2 days then compare compare to the second two. This is when the measure come in handy. To do this I calculate everything off the Max date.
     The measure would look like the following:
     Max Date = Max( Date )

     Then Calculate the Min date:
     Min Date = Min( Date)

     Count the number of days between dates:
     Date Delta = [Min Date] – [Max Date]

     Calculate half of the time range:
     Half Time Range = RoundDown( [Date Delta]/2 , 0 )
     – you do this because there will be time periods when there are an odd number of days, for example if your time period was 35 you would have to calculate your percent change between 17.5 days, this doesn’t make sense. Thus you round down, so when the time period is an odd number half the time period is a whole number. This makes 35 rounded down to 17 days.
     
     Calculate the middle date of the % change time period:
     Middle Date = [Max Date] – [Half Time Range]

     Calculate the end date:
     End Date = [Middle Date] – [Half Time Range]

     now we calculate the count of IDs with the date ranges we determined,
     Note: Period 1 Date range should be counting the values between [Max Date] and [Middle Date] and Period 2 Date range should be counting the values between [Middle Date] and [End Date]

     Create new measures
     Period 1 = Calculate( Count( Date ) , DatesBetween( Table , [Middle] , [Max Date] ) )
     Period 2 = Calculate( Count( Date ) , DatesBetween( Table , [End Date] , [Middle Date] )

     finally calculate your % change
     % change = Divide( [Period 1], [Period 2]) – 1

     Hope that helps.
     Mike

2. Dear Mike
   I am new to this blog. my apologies, if I am too direct.
   I have a problem for which I am unable to find a solution. I was wondering if you could give some solution, would be really appreciative.
   I am working in excel powerpivot, have slight knowledge of DAX.
   I have a table in which I have userIDs, and their level play details. The details include level no, start time and end time of each level. I want to calculate the session count and approx duration for each session.
   The sessions will be ofcourse for each individual user ID.
   A Session will/can include multiple levels, I am taking all levels that start within 5 minutes of completion of previous level as one sesssion.

   Waiting for any and all guidance.

   Best Regards/Aamer

   • This is a really tricky one. I think within PowerBI or Excel you could calculate the duration of each level. However, when you want to string together a number of user sessions and designate a time window of 5 minutes between each session your working with an indefinite amount. For example user 123 could have played level 1 (4min), 2 (3min), and 3 (5 min), for a total time of 15 minutes. From what i read above this would be a session. However, because of the way your data is structured you have to compare every end time to start time where the user id is the same. This now becomes more of a question that would have to be solved inside a database. I am not a aware of any methods that exist today that would let you build a session for a user.

     If I were going to build I would have to run the data through some sort of SQL data warehouse to aggregate the session info.

     Sorry that probably wasn’t to helpful. Good luck on your game.

3. Hi Mike,
   I am trying to rework your data and steps but I get an error which I cannot resolve myself…
   the error is just after having copied and pasted the query text into the advanced editor. The action starts and stop at Removed columns:

   = Table.RemoveColumns(#”Filtered Rows”,{“”, “2”})

   and in yellow:
   Expression.Error: The column ” of the table wasn’t found.
   Details:

   when I remove the step to remove columns another error occurs: 2011 is missing…

   so I started looking at the data source and apparently the url you used now contains very little data:

   Kind Name Text
   Element HTML *blank*

   Is there some way of getting insight into the correct webaddress – I do understand you depend on that source, but how to get to the right table would probably solve this item…

   Thanks for your tutorials so far and kind regards, Henrike

   • HI Henrike,

     You can use below link for Web “http://databank.worldbank.org/data/reports.aspx?source=2&series=NY.GDP.MKTP.PP.KD&country=”

4. Hi Mike,
   I am unable to get the data for the exercise but did manage to download the .csv file and after some removing of columns, transposing and fill-ing of data I have a file with a row for each country and a column for each year. Is there a way in PowerBI to get this in the Year, Country GDP format you use for the exercise?

   • I’m so sorry, the website has since changed how it stores it’s data. Thus, my old query no longer works. I have updated the query in the blog post. Also, Here is a copy of the new Query that you can enter into M:
     let
     Source = Excel.Workbook(Web.Contents(“https://powerbi.tips/wp-content/uploads/2017/11/Worldbank-DataSet.xlsx”), null, true),
     EconomicData_Table = Source{[Item=”EconomicData”,Kind=”Table”]}[Data],
     #”Changed Type” = Table.TransformColumnTypes(EconomicData_Table,{{“Country Name”, type text}, {“Country Code”, type text}, {“Indicator Name”, type text}, {“Indicator Code”, type text}, {“1960”, type number}, {“1961”, type number}, {“1962”, type number}, {“1963”, type number}, {“1964”, type number}, {“1965”, type number}, {“1966”, type number}, {“1967”, type number}, {“1968”, type number}, {“1969”, type number}, {“1970”, type number}, {“1971”, type number}, {“1972”, type number}, {“1973”, type number}, {“1974”, type number}, {“1975”, type number}, {“1976”, type number}, {“1977”, type number}, {“1978”, type number}, {“1979”, type number}, {“1980”, type number}, {“1981”, type number}, {“1982”, type number}, {“1983”, type number}, {“1984”, type number}, {“1985”, type number}, {“1986”, type number}, {“1987”, type number}, {“1988”, type number}, {“1989”, type number}, {“1990”, type number}, {“1991”, type number}, {“1992”, type number}, {“1993”, type number}, {“1994”, type number}, {“1995”, type number}, {“1996”, type number}, {“1997”, type number}, {“1998”, type number}, {“1999”, type number}, {“2000”, type number}, {“2001”, type number}, {“2002”, type number}, {“2003”, type number}, {“2004”, type number}, {“2005”, type number}, {“2006”, type number}, {“2007”, type number}, {“2008”, type number}, {“2009”, type number}, {“2010”, type number}, {“2011”, type number}, {“2012”, type number}, {“2013”, type number}, {“2014”, type number}, {“2015”, type number}, {“2016”, type number}, {“2017″, type any}}),
     #”Removed Other Columns” = Table.SelectColumns(#”Changed Type”,{“Country Name”, “2011”, “2012”, “2013”, “2014”, “2015”, “2016”}),
     #”Filtered Rows” = Table.SelectRows(#”Removed Other Columns”, each [2011] <> null and [2012] <> null and [2013] <> null and [2014] <> null and [2015] <> null and [2016] <> null),
     #”Unpivoted Other Columns” = Table.UnpivotOtherColumns(#”Filtered Rows”, {“Country Name”}, “Attribute”, “Value”),
     #”Renamed Columns” = Table.RenameColumns(#”Unpivoted Other Columns”,{{“Country Name”, “Country”}, {“Attribute”, “Year”}, {“Value”, “GDP”}})
     in
     #”Renamed Columns”

     This should allow you to complete the tutorial.

5. Thanks for this – it was concise and to the point!

1 Trackback / Pingback

1. #Excel Super Links #43 – shared by David Hager | Excel For You

Comments are closed.