cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
markus_zhang
Advocate II
Advocate II

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

(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:

login.png

 

I have a second table like this:

 

Table StructureTable 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

 

matrix.png

 

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~

 

1 ACCEPTED SOLUTION
markus_zhang
Advocate II
Advocate II

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])
    )
)

pivot.png

View solution in original post

2 REPLIES 2
markus_zhang
Advocate II
Advocate II

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])
    )
)

pivot.png

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])
    )

 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors