cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

PowerBI calculation using column values separated by 7 days

Hi,

 

I have scoured through the forums here but none of the solutions I have come across work for my case. I'd really appreciate any and all the help I can get as I am fairly new to PowerBI.

 

I'd like to generate the third and fourth columns in the table below (the 4th column's name is the Excel formula I'd like to implement in PowerBI):

 

Date NewObs  Obs7DaysPrior  LOG((NewObs/Obs7DaysPrior),2)
5/6/2020 67    
5/7/2020 68    
5/8/2020 69    
5/9/2020 70    
5/10/2020 71    
5/11/2020 72    
5/12/2020 80 67 0.255838904
5/13/2020 87 68 0.355480655
5/14/2020 87 69 0.334419039
5/15/2020 87 70 0.313660479
5/16/2020 87 71 0.293196376
5/17/2020 90 72 0.321928095
5/18/2020 90 80 0.169925001
5/19/2020 93 87 0.096215315
5/20/2020 95 87 0.126912112
5/21/2020 95 87 0.126912112
5/22/2020 95 87 0.126912112
5/23/2020 97 90 0.108059746

 

In the above calculation, the difference is 7 days, but it could be 14 or 30 or 5, depending on the requirements in future.

 

Thanks much,

Cobe.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

A couple comments:

 

Your example talks about 7 days prior but it actually shows 6 days prior.

Are you sure you want logarithm dualis?

Did you mean columns when you wrote columns?

 

Here's the approach according to your example.

LogN table in Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZFLDoMwDESvUrGuwJ/Y8ZwFcf9rNJUawEAWXVjK4j05nlnXyRZfhISm9/Rq4/X3OGZ7f6maqBhQkSgMKJypSgOKKWE8wjhhMsLkjMXtXprFLDRApRuajA7ut9OsZiXIzbpRng0chpbCIEU37NHYM2kGqzuVuhv+bPBxB5ThWr0bqTrckmo7hCFB2O+IRyOOX7EDYkTcjVQp9Po9mgkubMp9h6R22+qbweJgYZZu8N+G/G2kzlGvETSDggy1tHS3Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #" " = _t, NewObs = _t, #" .1" = _t, #" Obs7DaysPrior " = _t, #" .2" = _t, #"LOG((NewObs/Obs7DaysPrior),2)" = _t]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Date", "NewObs"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Date", type date}, {"NewObs", type number}})
in
    #"Changed Type"

 

Calculated Columns (!)

Obs7DaysPrior = 
var d = LogN[Date]
return CALCULATE(sum(LogN[NewObs]),all(LogN),LogN[Date]=d-6)

LogN = LOG(divide(LogN[NewObs],LogN[Obs7DaysPrior]),2)

 

And the result

lbendlin_0-1597789389943.png

 

 

View solution in original post

8 REPLIES 8
Super User II
Super User II

A couple comments:

 

Your example talks about 7 days prior but it actually shows 6 days prior.

Are you sure you want logarithm dualis?

Did you mean columns when you wrote columns?

 

Here's the approach according to your example.

LogN table in Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZFLDoMwDESvUrGuwJ/Y8ZwFcf9rNJUawEAWXVjK4j05nlnXyRZfhISm9/Rq4/X3OGZ7f6maqBhQkSgMKJypSgOKKWE8wjhhMsLkjMXtXprFLDRApRuajA7ut9OsZiXIzbpRng0chpbCIEU37NHYM2kGqzuVuhv+bPBxB5ThWr0bqTrckmo7hCFB2O+IRyOOX7EDYkTcjVQp9Po9mgkubMp9h6R22+qbweJgYZZu8N+G/G2kzlGvETSDggy1tHS3Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #" " = _t, NewObs = _t, #" .1" = _t, #" Obs7DaysPrior " = _t, #" .2" = _t, #"LOG((NewObs/Obs7DaysPrior),2)" = _t]),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Date", "NewObs"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Date", type date}, {"NewObs", type number}})
in
    #"Changed Type"

 

Calculated Columns (!)

Obs7DaysPrior = 
var d = LogN[Date]
return CALCULATE(sum(LogN[NewObs]),all(LogN),LogN[Date]=d-6)

LogN = LOG(divide(LogN[NewObs],LogN[Obs7DaysPrior]),2)

 

And the result

lbendlin_0-1597789389943.png

 

 

View solution in original post

Thanks for responding! The 7 days is just a placeholder as I indicated in my request. Feel free to use 6 if you'd like.

 

Yes, I'd like to use log(base 2) for the calculations.

 

I'm not sure about your question: "Did you mean columns when you wrote columns?" but yes, the four columns presented in the table. The first two are the available data and the last two are what I would like to calculate.

 

Could you help me walk through your LogN PowerQuery? It's completely new to me. Specifically, I'd like to understand how I can incorporate these pieces of code into my workbook:

 

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZFLDoMwDESvUrGuwJ/Y8ZwFcf9rNJUawEAWXVjK4j05nlnXyRZfhISm9/Rq4/X3OGZ7f6maqBhQkSgMKJypSgOKKWE8wjhhMsLkjMXtXprFLDRApRuajA7ut9OsZiXIzbpRng0chpbCIEU37NHYM2kGqzuVuhv+bPBxB5ThWr0bqTrckmo7hCFB2O+IRyOOX7EDYkTcjVQp9Po9mgkubMp9h6R22+qbweJgYZZu8N+G/G2kzlGvETSDggy1tHS3Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true])

and:

in type table [Date = _t, #" " = _t, NewObs = _t, #" .1" = _t, #" Obs7DaysPrior " = _t, #" .2" = _t, #"LOG((NewObs/Obs7DaysPrior),2)" = _t]),

 

Finally, is there a function similar to Excel's "IFERROR" to get rid of the "infinity" values"?

 

Thank you!

The Power Query code is to be taken as is, and put into the advanced editor of a blank query, replacing whatever boilerplate code is there.

 

The first two lines of that are basically the data and schema of your sample table.

 

Power BI has a concept of "calculated columns" and "measures" - those are two very different things with different computations. You chose columns, that's what the formulas serve up.

 

The DIVIDE() code allows for alternative results to avoid dividing by zero, and DAX also has IF(ISBLANK()) patterns to help you decide what to do in case nothing is returned.

Thank you. So the PowerQuery has created a table called "LogN" with only one column called "LogN", which stores only one text value, which is the PowerQuery code itself.

 

LogN.PNG

 

Not sure if this was intended.

 

No. Not like this.

 

Create a blank query. Right click it. Select Advanced Editor. Replace the existing code with the one from my post.

Thank you SO much! That worked.

Just a quick follow-up. I notice that the data is hard-coded from the json. How can I point NewsObs to the actual column in the data table that goes beyond these dates in my example?

Change the "Source = " line to point to your actual data.

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors