Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
markus_zhang
Advocate III
Advocate III

[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 III
Advocate III

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 III
Advocate III

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

AlB
Community Champion
Community Champion

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.