Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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...
Need Help
  • lbendlin's avatar
    lbendlin
    6 years ago

    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's avatar
lbendlin
Icon for Super User rankSuper User
6 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.

 

 

Not sure if this was intended.

 

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    6 years ago

    No. Not like this.

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you SO much! That worked.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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?

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        6 years ago

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