Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

backlog from start and end dates evaluated from the table for each row

Hi, I need to calculate a backlog value that evaluates for each row. For each document start date, I want to know how many other documents were open when that one opened. 

 

Item     Start Date      End Date      BackLog

a         1/01/2017     02/01/2017        1

b         1/10/2017     02/01/2017        2

c          02/02/2017   02/15/2017       0

d         01/20/2017    03/02/2017      3

 

etc.

 

I attempted at this, and it was close, but it appears like it is calculating the expression 1 time and filling that value down rather than re-evaluating for each row. (value is a dummy column filled with 1 just for me to count) Backlog = CALCULATE(count(Table[Value]),FILTER(Table, Table[StartDate]>=Table[StartDate2]),FILTER(Table, Table[EndDate]<Table[StartDate]))

 

Thanks in advance for any help!

EW

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi there,

     

    I have not tried this and I am not sure if this what you want but you can try:

    Backlog = 
VAR startDate = Table[StartDate]
RETURN
COUNTROWS(
  FILTER(
    Table,
    Table[StartDate] <= startDate && startDate <= Table[EndDate]
  )
)

    Regards,

     

    Kristjan

7 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi Anonymous

    I cannot understand, amongst others, what DocumentTracker[StartDate] or Table[StartDate2] is.

    Maybe if you provide a sample file with all the tables and elaborate a bit further on what you need we'd be able to help.

     

    Best

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I apologize, that was a typo that I edited and cleaned up; I was replacing my names with generic "Table" etc.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi there,

     

    I have not tried this and I am not sure if this what you want but you can try:

    Backlog = 
VAR startDate = Table[StartDate]
RETURN
COUNTROWS(
  FILTER(
    Table,
    Table[StartDate] <= startDate && startDate <= Table[EndDate]
  )
)

    Regards,

     

    Kristjan

    • AlB's avatar
      AlB
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Anonymous

      Anonymous

      I think you would have to take into account that with Anonymous's solution you would be counting the document in the current row as well. Depending on what exactly you're looking for, you might want to subtract 1.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you so much, this worked because the error that I had been getting was indicating that I was not allowed to refer to 2 different columns in my expression and you got around that by using a variable.  

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      is it possible to do loops in power BI also or are they not needed because of the way measures vs. columns evaluate?

       

      I was next going to describe how I might word it in a language like python as a while loop instead

      • AlB's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Anonymous

        What are you looking for exactly? FILTER() for example is a kind of loop as it iterates over the rows of a table. So are the iterators SUMX, AVERAGEX, etc