Help to calculate growth rate per hour and day

[Bit2021]

I have a table like the below:

 The value of "Amount of records ..." is the last number of records inside the table.

Note: I have a job that runs a "select count(*) from table1" query and then inserts it into a table with date and time.

Now, I want to calculate the growth rate per hour and day like the below screenshot:

How can I do this with Power BI? 

[Anonymous · Accepted solution]

@Bit2021 
In this case, you need to create datetime column otherwise the calculate needs very complex filter condition because the time and date are separated. See pbix if needed.

1. DateTime = CONCATENATE([Date],CONCATENATE(" ",[Time]))

2. New =

var previous_datetime = CALCULATE(MAX([DateTime]),FILTER('Table',[DateTime]<EARLIER('Table'[DateTime])))

Return IF([DateTime]=MINX(ALL('Table'),[DateTime]),[Records]-3050,[Records]-CALCULATE(MAX([Records]),FILTER('Table',[DateTime]=previous_datetime)))

3. Growth =

var previous_datetime = CALCULATE(MAX([DateTime]),FILTER('Table',[DateTime]<EARLIER('Table'[DateTime])))

Return IF([DateTime]=MINX(ALL('Table'),[DateTime]),[New]/3050,[New]/CALCULATE(MAX('Table'[Records]),FILTER('Table',[DateTime]=previous_datetime)))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Bit2021 

Create the following columns, and change the decimal value to percent in the format.

New = IF([Date]=MINX(ALL('Table'),[Date]),

[Records]-5100,

[Records]-CALCULATE(MAX([Records]),FILTER('Table',[Date]=EARLIER('Table'[Date])-1)))

Growth = IF([Date]=MINX(ALL('Table'),[Date]),[New]/5100,[New]/CALCULATE(MAX('Table'[Records]),FILTER('Table',[Date]=EARLIER([Date])-1)))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Bit2021]

Thanks for your reply, I have a time column like the below, which change should id do in your code?

the default value = 3050

ex: 3341-3050 = 291

3874 - 3341 = 533

....

[Greg_Deckler]

@Bit2021 Seems like a variation on MTBF. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[Bit2021]

Thanks for your reply. I use the below method but I think I couldn't understand it very well and I received the wrong result.

Just for clarifying, I show my sample request in Excel:

I have a table (white background) and my calculate columns are yellow background.

New records = Current - Previous 

Ex: 5100-5214 = 114

Growth rate:

((5214-5100)/5100) * 100 = 2.23