Measure for calculating row by row sums based on dates

[eddd83]

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?

[eddd83]

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/2019	4
1/8/2019	5
1/15/2019	8
1/22/2019	9
1/29/2019	9

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 · Accepted solution]

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]

That seems to do the trick, thanks!

[v-yulgu-msft]

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