Forum Discussion

eddd83's avatar
eddd83
Icon for Resolver I rankResolver I
7 years ago
Solved

Measure for calculating row by row sums based on dates

I need to produce a table similar to this spreadsheet

 

 

So far, I just have this very empty table of dates based on a seperate "date" table. 

 

The "date" table in question.

 

 

 

The crux of my problem: basically, in the "task" table I need to compare the "due date" column to the "task completion date" column, and then do a sum based on the row context in the 2nd picture above. The fact that I have two seperate tables ("date" & "task") only further complicates my problem. 

 

Help?

Need Help
  • d_gosbell's avatar
    d_gosbell
    7 years ago

    You can do this all in the one Power BI model without exporting/importing.

     

    First thing I would do is to create a separate date table. You can do this in multiple ways, but one of the easiest is to create a calculated table using the CALENDAR() function. Then you would use this table (or columns from this table) to do all your date filtering on your report.

     

    Then you would create a measure like the following

    Overdue Tasks = COUNTROWS(
    filter(tasks
        , Tasks[Due Date] <= MAX('Date'[Date]) 
        && if(ISBLANK(Tasks[Completion Date]),date(2999,12,31),Tasks[Completion Date])>= min('Date'[Date]) 
    )
)

    This produces the following output (the output is on the left, my test data is on the right)

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi eddd83 ,

     

    What is the desired result? For example, in above second table visual, what is the corrsponding value in the first row for date "February,1, 2018", then for date "February, 8, 2018"? Please illustrate with examples.

     

    Best regards,

    Yuliana Gu

  • eddd83's avatar
    eddd83
    Icon for Resolver I rankResolver I
    7 years ago

    Basically, I want to see how many tasks are overdue as of that date. If the task was due on 1/1/2019 and the task was also completed on 1/1/2019, then it wouldn't count as being overdue.

     

    For example, for 1/1/2019, the 1st row with task "Validation of OE Facility Register" has a due date of 1/1/2019 and task completion date of 1/10/2019. Therefore, it would count as 1 towards 1/1/2019

     

    Another example, for 1/29/2019, the last row with task "Report Monthly LPS Metrics" has a due date of 1/28/2019 and task completion date of 1/18/2019. Therefore, it would count as 0 towards 1/29/2019.

     

     Count
    1/1/20194
    1/8/20195
    1/15/20198
    1/22/20199
    1/29/20199

     

     

     

    Just curious if this can be entirely done in power bi. I can envision a different scenario where you export the data table (2nd picture) into a spreadsheet with array formulas. Then output that same spreadsheet with calculated values back into power bi. 

    • d_gosbell's avatar
      d_gosbell
      Icon for Super User rankSuper User
      7 years ago

      You can do this all in the one Power BI model without exporting/importing.

       

      First thing I would do is to create a separate date table. You can do this in multiple ways, but one of the easiest is to create a calculated table using the CALENDAR() function. Then you would use this table (or columns from this table) to do all your date filtering on your report.

       

      Then you would create a measure like the following

      Overdue Tasks = COUNTROWS(
    filter(tasks
        , Tasks[Due Date] <= MAX('Date'[Date]) 
        && if(ISBLANK(Tasks[Completion Date]),date(2999,12,31),Tasks[Completion Date])>= min('Date'[Date]) 
    )
)

      This produces the following output (the output is on the left, my test data is on the right)

      • eddd83's avatar
        eddd83
        Icon for Resolver I rankResolver I
        7 years ago

        That seems to do the trick, thanks!