Forum Discussion

RobertKuij's avatar
RobertKuij
Frequent Visitor
6 years ago
Solved

Previousday function problem

Dear users, 

 

I have been working on building a stock analysis dashboard, but I am struggling finding the previous day values as well as the percentage change. Problem

 

1) the Previousday function does not return a value. Other methods only returns the value of that day. 

2) Percentage change function. 

 

The query looks like this: 

 

 

 

Thanks in advance!

 

Robert

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    RobertKuij , that will work better a Measure for  a new column try

     

    new column = sumx(filter(table, table[date]=earlier(table[date])-1),[closing])

     

    Use last, not the yesterday's date

    last Date= maxx(filter(table, table[date]<earlier(table[date])),[date])
    new column = sumx(filter(table, table[date]=earlier(table[last date])-),[closing])

     

     

    Measure can be like

    Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))
    Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))

4 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi RobertKuij 

    remove .[Date]:

    Previous Day = CALCULATE(SUM(AEX[Closing]), PREVIOUSDAY(AEX[Date]))
    • RobertKuij's avatar
      RobertKuij
      Frequent Visitor
      6 years ago

      Thanks for your reply, but unfortunately that also does not seem to work.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    RobertKuij , that will work better a Measure for  a new column try

     

    new column = sumx(filter(table, table[date]=earlier(table[date])-1),[closing])

     

    Use last, not the yesterday's date

    last Date= maxx(filter(table, table[date]<earlier(table[date])),[date])
    new column = sumx(filter(table, table[date]=earlier(table[last date])-),[closing])

     

     

    Measure can be like

    Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))
    Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))

    • RobertKuij's avatar
      RobertKuij
      Frequent Visitor
      6 years ago

      Thanks that really helped!

       

      I do have an additional question. These measures would only be useful for one query right? As I have to add another 39 stocks into my dashboard, this would mean I have to create these measures for every stock. Is it possible to create a column in the Power Query Editor so that making these measures for every new stock would not be necessary?