CODEWITHSUNDEEP
powerbidax
Hessam P.
Hessam P.

Reputation: 11

need to find new rows based on the date with DAX in power BI

I need to count "User" based on the "Date" to find newly added users. Let`s imagine July 1, 2021 is the starting date of data. Sample data like:

user    date    count of login
    a   Thursday, July 1, 2021  10
    b   Thursday, July 1, 2021  5
    c   Thursday, July 1, 2021  4
    d   Thursday, July 1, 2021  1
    e   Thursday, July 1, 2021  1
    a   Saturday, July 10, 2021 5
    b   Saturday, July 10, 2021 10
    c   Saturday, July 10, 2021 2
    g   Saturday, July 10, 2021 5
    h   Saturday, July 10, 2021 4
    g   Monday, July 12, 2021   2
    h   Monday, July 12, 2021   2
    i   Monday, July 12, 2021   2

in this case the output should be like below:

Date    count of new user
July 1, 2021     5
July 10, 2021     2
July 12, 2021     1

Somehow count the user when it never appeared in the all previous dates( or count the first appearance as a new user and ignore all the subsequent)

Appreciation for any help

Upvotes: 0

Views: 193

Answers (3)

smpa01
smpa01

Reputation: 4282

Please try out the following code

_newCustomers2 = 
VAR _currentDate =
    MAX ( 'fact'[date] )
VAR _distinctNewCount =
    COUNTROWS (
        EXCEPT (
            VALUES ( 'fact'[user] ),
            SUMMARIZE (
                FILTER ( ALL ( 'fact' ), 'fact'[date] <_currentDate ),
                'fact'[user]
            )
        )
    )
RETURN
    _distinctNewCount

if you have a table like following

| user | date       | Index |
|------|------------|-------|
| a    | 2021-07-01 | 1     |
| b    | 2021-07-01 | 2     |
| c    | 2021-07-01 | 3     |
| d    | 2021-07-01 | 4     |
| e    | 2021-07-01 | 5     |
| a    | 2021-07-10 | 6     |
| b    | 2021-07-10 | 7     |
| c    | 2021-07-10 | 8     |
| g    | 2021-07-10 | 9     |
| h    | 2021-07-10 | 10    |
| g    | 2021-07-12 | 11    |
| h    | 2021-07-12 | 12    |
| i    | 2021-07-12 | 13    |
| a    | 2021-07-12 | 14    |
| d    | 2021-07-12 | 15    |
| e    | 2021-07-12 | 16    |
| d1   | 2021-07-12 | 17    |
| e1   | 2021-07-12 | 18    |
| g    | 2021-07-18 | 19    |
| h    | 2021-07-18 | 20    |
| i    | 2021-07-18 | 21    |
| a    | 2021-07-18 | 22    |
| a1   | 2021-07-18 | 23    |
| a    | 2021-07-19 | 24    |
| a1   | 2021-07-19 | 25    |
| c    | 2021-07-19 | 26    |

the above code returns the count of the highlighted rows SOlution

Solution2

Upvotes: 0

Mr.Batra
Mr.Batra

Reputation: 833

This should work as per requirements.

New_User_Count = 

VAR currentDate =
    MAX ( 'Sheet1'[date] )

VAR preceedingDates = CALCULATETABLE(VALUES(Sheet1[date]),Sheet1[date] < currentDate)

VAR distinctNewCount =
    COUNTROWS (
        EXCEPT (
            VALUES ( 'Sheet1'[user] ),
            SUMMARIZE (
                FILTER ( ALL ( 'Sheet1' ), 'Sheet1'[date] in preceedingDates ),
                'Sheet1'[user]
            )
        )
    )
RETURN distinctNewCount

Upvotes: 0

Hessam P.
Hessam P.

Reputation: 11

I got the point, For future users... sheet1 is the name of excel sample file

VAR __CURRENT_VALUES = SUMMARIZE(VALUES('Sheet1'), 'Sheet1'[user])
VAR __EXISTING_VALUES =
        CALCULATETABLE(
            SUMMARIZE(VALUES('Sheet1'), 'Sheet1'[user]),
            FILTER(
                ALL('Sheet1'[date].[Date]),
                'Sheet1'[date].[Date] < MIN('Sheet1'[date].[Date])
            ),
            KEEPFILTERS(__CURRENT_VALUES)
        )
    RETURN
        CALCULATE(
            DISTINCTCOUNT('Sheet1'[user]),
            EXCEPT(__CURRENT_VALUES, __EXISTING_VALUES)
        )

Upvotes: 1

Related Questions