Using historical dates to calculate system status at intervals in the past

[DarrellK]

Hi,

I have three main entities: Sites (different websites), that each have many Pages, that each have many Reviews - i.e. records of when they were reviewed in the past. I'm wanting to count the number of Pages in each Site that were overdue for review at different points in the past (at six monthly intervals in the past). The ideas is to track how the number of items overdue trended up and down for each site in the past. For a Page to be overdue for review, if must be greater than 3 years since the date the last review completed.

I want to end up with a matrix something like this, where the numbers in the grid are the number of pages that were overdue for review at each date, for each Site

I've created the following measure that works correctly for any single date entered as dateToWorkFrom . If I set this to TODAY(), I get the number of items currently overdue. If I set this to DATE(2019, 7, 1), I get the number of items that were overdue on 1 July 2019.

Pages > 3 years since reviewed for date = 
VAR dateToWorkFrom = DATE(2019, 7, 1) //TODAY()
VAR date3YearsPreviously = dateToWorkFrom - (365*3)

RETURN
CALCULATE(
    COUNT(Pages[PageKey]),
    FILTER(
        Pages, 
        VAR pageKey = Pages[PageKey]     
        RETURN
            CALCULATE(
                MAX(Reviews[DateReviewCompleted]),
                FILTER(Reviews, 
                    Reviews[PageKey] = pageKey &&  
                    Reviews[DateReviewCompleted] < dateToWorkFrom
                )
            ) < date3YearsPreviously
    )
)

 So if I enter any one date, I get the correct result for each Site. However, thus far I have not been successful in creating a matrix of results for all dates at once.

Any suggestions much appreciated!

[DarrellK]

I did come up with a solution, but more complex than I hoped (though still wonder if there is a simpler solution).

I had this table called DateHalfYear to start with - a simplified date-type table with dates each six months for a few years:

I then created a new table called PagesWithDates in Power Query, that included a row for each date in the above table for each page record (total rows in new table = number dates from above table x total page records):

let
    Source = DateHalfYearDim,
    RemoveCols = Table.SelectColumns(Source,{"DateKey"}),
    AddPagesFactAsColumn = Table.AddColumn(RemoveCols, "Pages", each Pages),
    ExpandPages = Table.ExpandTableColumn(AddPagesAsColumn, "Pages", {"PageKey", "SiteKey"}, {"PageKey", "SiteKey"}),
    ChangeTypes = Table.TransformColumnTypes(ExpandPages,{{"PageKey", Int64.Type}, {"SiteKey", Int64.Type}})
in
    ChangeTypes

I then hooked up the new PagesWithDates fact table to the relevant dimension tables:

And added a calculated column to PagesWithDates table to work out the months since last review for each page record at each historical date:

Months since last review = 
VAR dateToWorkFrom = RELATED(DateHalfYear[Date])

VAR pageKey = PagesWithDates[PageKey]
VAR lastReviewedDate =      
    CALCULATE(
        MAX(Reviews[DateReviewCompleted]),
        FILTER(Reviews, 
            Reviews[PageKey] = pageKey &&  
            Reviews[DateReviewCompleted] < dateToWorkFrom
        )
    )

VAR monthsSinceLastReview = IF(NOT ISBLANK(lastReviewedDate), DATEDIFF(lastReviewedDate, dateToWorkFrom, MONTH))

RETURN
    monthsSinceLastReview

Then created a measure to count the number of pages more than 3 years since last reviewed

Number pages greater than 3 years since reviewed = 
COUNTROWS(
    FILTER(
        PagesWithDates,
        PagesWithDates[Months since last review] > 36
    )
)

(I guess a more complex measure could be used to eliminate the need for a calculated column, but I found it simpler to think of it this way)

Lastly, I created a matrix from:

• Rows: Site[Site name]
• Columns: DateHalfYear[Year] and DateHalfYear[Month name short]
• Values: [Number pages greater than 3 years since reviewed]

This has produced a result that is sufficiently accurate for my purposes and that shows the trends over time with respect to the number of pages that were 'overdue' for review (i.e. more than 3 years since last review) at each historical date, with dates at intervals of six months.

Any comments on whether such a complex solution was needed, or refinements to the above solution are appreciated.

[lbendlin · Accepted solution]

"Perhaps the dates used to created the matrix columns need to be related to the other tables used in the DAX formula?"

Exactly.  If you see the same number over and over you know you picked the wrong field, from a dangling table or from the wrong end of a search direction.

[DarrellK]

Thanks for that. I'll mark the combination of your comments as the solution. I've added details of the evental way I solved this. Any comments on that welcome.

[lbendlin]

VAR dateToWorkFrom = SELECTEDVALUE(<your date from the visual or data model>)
VAR date3YearsPreviously = EDATE(dateToWorkFrom,-36)

[DarrellK]

Thanks, @lbendlin .

Using EDATE is much neater and more accurate.

I did try using SELECTEDVALUE() to grab the date from each column in the matrix. I created the matrix columns from a separate table, unrelated to the other tables in the model. SELECTEDVALUE() did pull out the date correctely for each cell in each column when that was all I displayed in the matrix, but when fed these dates into the DAX formula in my original post, the results made no sense. There is something I don't understand here regarding how the model interacts with the DAX. Perhaps the dates used to created the matrix columns need to be related to the other tables used in the DAX formula?

Anyway, I did come up with a solution, but more complex than I hoped - I'll add the details in a separate post below. I'd still like to understand why the simpler solution did not work and if there is a way to get it to work.