[I think solved] How can I get the previous date in a Power BI Matrix

[markus_zhang]

(I think I solved it, in the first reply, will validate when I'm back to the company)

Hi experts,

Sorry for the ambigous title, I think it would be better for an example:

I have a login record table like this. It has every login record for each member email, so there are a lot of duplicates in the email column:

I have a second table like this:

Table Structure

In which email is a unique list of member emails, Spender_Status indicates if it's a paying member or not, and the rest are calculated columns using the data from the first table to ease the writing of measures.

Now I also have a Matrix with a continous date table (column), for now I filter by week, but I expect users to filter whatever dates they need, and not neccesarily with the same period. 

Question:

I'd like to put a measure into the matrix to show the following:

Each cell should show Total Number of Logins of Members who joined in that period.

For example, on row "Wednesday, December 26, 2018", it is supposed to show the total number of logins of members who joined in (December 12, December 19].

Now I know I can use SELECTEDVALUE() to grab a single date from the Matrix. It is also very easy to write a measure to grab the total number of logins of members who joined in and before that period. However I have idea how to continue from here.

Basically, I think I need to do this:

Try to grab the previous date (row above) and it's easy to go from there (I googled a while, found a solution only for fixed date column, which is not good enough).

I need to grab all members joined in that period and then it's simply a query to Table 2 because Table 2 has a calculated column showing the date of first sign-up event. And then I will query Table 1 to grab all the joins of those specific users, going to be slow but doable.

I appreciate any help, thank you in advance. BTW I'm starting to realize the importance of data model, as writing different categories of data seems to need different shapes of data model, otherwise it's going to have long and slow measures.

**Updated** At the end of writing this post I realized maybe I could try to SUMMARIZE the tables with the SELECTEDVALUE() column, just maybe...Or maybe I could use ALLSELECTED() and RANK(), I'll try both tomorrow. Good night~

[markus_zhang · Accepted solution]

OK guys, figured it out, only the first row needs some extra code but I'm going to do that this morning:

WeekMatrix[Date] is the date column in pivot

_PreviousPivotSelection = 
//  Should return the previous selection in pivot
CALCULATE(
    MAX(WeekMatrix[Date]),
    FILTER(
        ALLSELECTED(WeekMatrix[Date]),
        WeekMatrix[Date] < SELECTEDVALUE(WeekMatrix[Date])
    )
)

[AlB]

Hi @markus_zhang

Nice. I do not think you need the ALLSELECTED( ) though. An ALL( ) should suffice:

_PreviousPivotSelection = 
//  Should return the previous selection in pivot
CALCULATE(
    MAX(WeekMatrix[Date]),
    FILTER(
        ALL(WeekMatrix[Date]),
        WeekMatrix[Date] < SELECTEDVALUE(WeekMatrix[Date])
    )