topic Re: PowerBI calculation using column values separated by 7 days in Desktop
https://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1305573#M567471
<P>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.</P>
<P> </P>
<P>The first two lines of that are basically the data and schema of your sample table.</P>
<P> </P>
<P>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.</P>
<P> </P>
<P>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.</P>Tue, 18 Aug 2020 23:43:29 GMTlbendlin2020-08-18T23:43:29ZPowerBI calculation using column values separated by 7 days
https://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1305258#M567392
<P>Hi,</P><P> </P><P>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.</P><P> </P><P>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):</P><P> </P><TABLE><TBODY><TR><TD>Date</TD><TD> </TD><TD>NewObs</TD><TD> </TD><TD> Obs7DaysPrior </TD><TD> </TD><TD>LOG((NewObs/Obs7DaysPrior),2)</TD></TR><TR><TD>5/6/2020</TD><TD> </TD><TD>67</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>5/7/2020</TD><TD> </TD><TD>68</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>5/8/2020</TD><TD> </TD><TD>69</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>5/9/2020</TD><TD> </TD><TD>70</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>5/10/2020</TD><TD> </TD><TD>71</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>5/11/2020</TD><TD> </TD><TD>72</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR><TD>5/12/2020</TD><TD> </TD><TD>80</TD><TD> </TD><TD>67</TD><TD> </TD><TD>0.255838904</TD></TR><TR><TD>5/13/2020</TD><TD> </TD><TD>87</TD><TD> </TD><TD>68</TD><TD> </TD><TD>0.355480655</TD></TR><TR><TD>5/14/2020</TD><TD> </TD><TD>87</TD><TD> </TD><TD>69</TD><TD> </TD><TD>0.334419039</TD></TR><TR><TD>5/15/2020</TD><TD> </TD><TD>87</TD><TD> </TD><TD>70</TD><TD> </TD><TD>0.313660479</TD></TR><TR><TD>5/16/2020</TD><TD> </TD><TD>87</TD><TD> </TD><TD>71</TD><TD> </TD><TD>0.293196376</TD></TR><TR><TD>5/17/2020</TD><TD> </TD><TD>90</TD><TD> </TD><TD>72</TD><TD> </TD><TD>0.321928095</TD></TR><TR><TD>5/18/2020</TD><TD> </TD><TD>90</TD><TD> </TD><TD>80</TD><TD> </TD><TD>0.169925001</TD></TR><TR><TD>5/19/2020</TD><TD> </TD><TD>93</TD><TD> </TD><TD>87</TD><TD> </TD><TD>0.096215315</TD></TR><TR><TD>5/20/2020</TD><TD> </TD><TD>95</TD><TD> </TD><TD>87</TD><TD> </TD><TD>0.126912112</TD></TR><TR><TD>5/21/2020</TD><TD> </TD><TD>95</TD><TD> </TD><TD>87</TD><TD> </TD><TD>0.126912112</TD></TR><TR><TD>5/22/2020</TD><TD> </TD><TD>95</TD><TD> </TD><TD>87</TD><TD> </TD><TD>0.126912112</TD></TR><TR><TD>5/23/2020</TD><TD> </TD><TD>97</TD><TD> </TD><TD>90</TD><TD> </TD><TD>0.108059746</TD></TR></TBODY></TABLE><P> </P><P>In the above calculation, the difference is 7 days, but it could be 14 or 30 or 5, depending on the requirements in future.</P><P> </P><P>Thanks much,</P><P>Cobe.</P>Tue, 18 Aug 2020 19:59:41 GMThttps://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1305258#M567392cobe20202020-08-18T19:59:41ZRe: PowerBI calculation using column values separated by 7 days
https://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1305467#M567445
<P>A couple comments:</P>
<P> </P>
<P>Your example talks about 7 days prior but it actually shows 6 days prior.</P>
<P>Are you sure you want logarithm dualis?</P>
<P>Did you mean columns when you wrote columns?</P>
<P> </P>
<P>Here's the approach according to your example.</P>
<P>LogN table in Power Query:</P>
<LI-CODE lang="java">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"</LI-CODE>
<P> </P>
<P>Calculated Columns (!)</P>
<LI-CODE lang="java">Obs7DaysPrior =
var d = LogN[Date]
return CALCULATE(sum(LogN[NewObs]),all(LogN),LogN[Date]=d-6)
LogN = LOG(divide(LogN[NewObs],LogN[Obs7DaysPrior]),2)</LI-CODE>
<P> </P>
<P>And the result</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="lbendlin_0-1597789389943.png" style="width: 400px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/336108iEBEA3E3AE4A19703/image-size/medium?v=1.0&px=400" role="button" title="lbendlin_0-1597789389943.png" alt="lbendlin_0-1597789389943.png" /></span></P>
<P> </P>
<P> </P>Tue, 18 Aug 2020 22:23:29 GMThttps://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1305467#M567445lbendlin2020-08-18T22:23:29ZRe: PowerBI calculation using column values separated by 7 days
https://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1305565#M567469
<P>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.</P><P> </P><P>Yes, I'd like to use log(base 2) for the calculations.</P><P> </P><P>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.</P><P> </P><P>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:</P><P> </P><PRE>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])</PRE><P>and:</P><PRE>in type table [Date = _t, #" " = _t, NewObs = _t, #" .1" = _t, #" Obs7DaysPrior " = _t, #" .2" = _t, #"LOG((NewObs/Obs7DaysPrior),2)" = _t]),</PRE><P> </P><P>Finally, is there a function similar to Excel's "IFERROR" to get rid of the "infinity" values"?</P><P> </P><P>Thank you!</P>Tue, 18 Aug 2020 23:32:47 GMThttps://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1305565#M567469cobe20202020-08-18T23:32:47ZRe: PowerBI calculation using column values separated by 7 days
https://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1305573#M567471
<P>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.</P>
<P> </P>
<P>The first two lines of that are basically the data and schema of your sample table.</P>
<P> </P>
<P>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.</P>
<P> </P>
<P>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.</P>Tue, 18 Aug 2020 23:43:29 GMThttps://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1305573#M567471lbendlin2020-08-18T23:43:29ZRe: PowerBI calculation using column values separated by 7 days
https://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1305589#M567476
<P>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.</P><P> </P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="LogN.PNG" style="width: 413px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/336151i392234E732F74E8A/image-size/large?v=1.0&px=999" role="button" title="LogN.PNG" alt="LogN.PNG" /></span></P><P> </P><P>Not sure if this was intended.</P><P> </P>Wed, 19 Aug 2020 00:05:50 GMThttps://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1305589#M567476cobe20202020-08-19T00:05:50ZRe: PowerBI calculation using column values separated by 7 days
https://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1305606#M567479
<P>No. Not like this.</P>
<P> </P>
<P>Create a blank query. Right click it. Select Advanced Editor. Replace the existing code with the one from my post.</P>Wed, 19 Aug 2020 00:34:52 GMThttps://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1305606#M567479lbendlin2020-08-19T00:34:52ZRe: PowerBI calculation using column values separated by 7 days
https://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1324065#M572691
<P>Thank you SO much! That worked.</P>Tue, 25 Aug 2020 23:07:38 GMThttps://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1324065#M572691cobe20202020-08-25T23:07:38ZRe: PowerBI calculation using column values separated by 7 days
https://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1324111#M572706
<P>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?</P>Tue, 25 Aug 2020 23:45:55 GMThttps://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1324111#M572706cobe20202020-08-25T23:45:55ZRe: PowerBI calculation using column values separated by 7 days
https://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1324119#M572708
<P>Change the "Source = " line to point to your actual data.</P>Tue, 25 Aug 2020 23:48:22 GMThttps://community.powerbi.com/t5/Desktop/PowerBI-calculation-using-column-values-separated-by-7-days/m-p/1324119#M572708lbendlin2020-08-25T23:48:22Z