Flag if value existed week ago or not

[MatejZukovic]

Hi,

I have data set with 2 columns - Week and User. Data indicates weeks when users have been part of certain group. I would like to create new column (New User) that will compare Week with previous week. If the user is not found in a previous week, I'd like to return "Yes". If user can be found in table in previous week, I'd like to return "No". 

Example: For Week 2 I'd like to see "Yes" for John as there is no data showing John was part of the group in Week 1. For Week 3, I'd like to see "No" because John was part of a group in Week 2.

Hope it's clear enough 🙂

Any help is appreciated.

Thanks!

Matej

[fhill · Accepted solution]

New User = IF( ISBLANK( CALCULATE( SUM('Table'[Week]), FILTER(ALL('Table'), 'Table'[User] = EARLIER('Table'[User])

&& 'Table'[Week] = (EARLIER('Table'[Week]) -1) )) ) , "YES", "NO")

Let me know if this works?  *** Calculations like this are DEPENDENT on your data being Properly SORTED First! **

P.S.  Do we need to take into account First / Last week of the year?   (if Week 1 -1 woudl be 0, look for 52 instead?)

[amitchandak · Accepted solution]

@MatejZukovic , measure approch is this week vs last week, you can week rank of week but need to separate table

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

Column way =

new user =if(isblank(countx(filter(table,[user]=earlier([user]) && [week] =earlier([week]) -1),[user])),"Yes","No")

[amitchandak · Accepted solution]

@MatejZukovic , measure approch is this week vs last week, you can week rank of week but need to separate table

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

Column way =

new user =if(isblank(countx(filter(table,[user]=earlier([user]) && [week] =earlier([week]) -1),[user])),"Yes","No")

[lkalawski]

@MatejZukovic ,

Try this measure:

New User =
VAR _prweek =
    SELECTEDVALUE ( Users[Week] ) - 1
VAR _user =
    SELECTEDVALUE ( Users[User] )
VAR _Cnt =
    CALCULATE (
        COUNTROWS ( Users ),
        FILTER ( ALL ( Users ), Users[Week] = _prweek && Users[User] = _user )
    )
RETURN
    SWITCH ( TRUE (), _Cnt > 0, "No", "Yes" )

_______________
If I helped, please accept the solution and give kudos! 😀

[fhill · Accepted solution]

New User = IF( ISBLANK( CALCULATE( SUM('Table'[Week]), FILTER(ALL('Table'), 'Table'[User] = EARLIER('Table'[User])

&& 'Table'[Week] = (EARLIER('Table'[Week]) -1) )) ) , "YES", "NO")

Let me know if this works?  *** Calculations like this are DEPENDENT on your data being Properly SORTED First! **

P.S.  Do we need to take into account First / Last week of the year?   (if Week 1 -1 woudl be 0, look for 52 instead?)

[MatejZukovic]

Hi @fhill , your solution works perfectly. Great point on first / last week of the year. How would you approach it? Also, can you elaborate bit more on data must be sorted properly?

Thanks a ton,

Matej

[fhill · Accepted solution]

Sorry, ignore the 'Sory' comment, I was thinking of a differnt situation...

Obvioustly there's multiple way to solve this based on all the posts.  Here's one way to take YEARS and Weeks into consideration.

New User = IF ( TableByUser[Week] <> 1,

IF( ISBLANK( CALCULATE( SUM('TableByUser'[Week]), FILTER(ALL('TableByUser'), 'TableByUser'[User] = EARLIER('TableByUser'[User]) && TableByUser[Year] = EARLIER(TableByUser[Year])

&& 'TableByUser'[Week] = (EARLIER('TableByUser'[Week]) -1) )) ) , "YES", "NO")

, IF( ISBLANK( CALCULATE( SUM('TableByUser'[Week]), FILTER(ALL('TableByUser'), 'TableByUser'[User] = EARLIER('TableByUser'[User]) && TableByUser[Year] = (EARLIER(TableByUser[Year]) - 1)

&& 'TableByUser'[Week] = 52 )) ) , "YES", "NO")