如何在Power BI中使用DAX按日期统计新增用户

Got it, let's figure out how to calculate the number of new users per date in Power BI using DAX—where a "new user" is someone who's never appeared in any earlier dates (starting from July 1, 2021). Here are two solid approaches you can use:

Approach 1: Using a Calculated Column + Measure #

This method is straightforward and easy to debug, since we first capture each user's first login date.

Step 1: Add a Calculated Column for First Login Date #

In your user login table (let's assume it's named UserLogins), create this calculated column to store the earliest date each user ever logged in:

First Login Date = 
CALCULATE(
    MIN(UserLogins[date]),
    ALLEXCEPT(UserLogins, UserLogins[user])
)

• ALLEXCEPT keeps only the current user's context, so we're looking at all records for that specific user.
• MIN(UserLogins[date]) grabs their very first login date.

Step 2: Create the New User Count Measure #

Now make a measure to count how many users have their first login date matching the current date in your date filter:

Count of New Users = 
VAR CurrentDate = MAX('Date'[Date]) -- Use your date dimension table here
RETURN
CALCULATE(
    DISTINCTCOUNT(UserLogins[user]),
    UserLogins[First Login Date] = CurrentDate,
    UserLogins[date] >= DATE(2021, 7, 1) -- Enforce our start date
)

Approach 2: Pure Measure (No Calculated Column) #

If you prefer to avoid adding calculated columns, this all-in-one measure works just as well:

Count of New Users (No Calc Column) = 
VAR CurrentDate = MAX('Date'[Date])
-- Get all users who logged in before the current date (on/after our start date)
VAR UsersBeforeCurrent = 
    CALCULATETABLE(
        DISTINCT(UserLogins[user]),
        UserLogins[date] < CurrentDate,
        UserLogins[date] >= DATE(2021, 7, 1)
    )
-- Get all users who logged in on the current date
VAR UsersOnCurrent = 
    CALCULATETABLE(
        DISTINCT(UserLogins[user]),
        UserLogins[date] = CurrentDate,
        UserLogins[date] >= DATE(2021, 7, 1)
    )
-- Return the count of users who are in UsersOnCurrent but NOT in UsersBeforeCurrent
RETURN
COUNTROWS(EXCEPT(UsersOnCurrent, UsersBeforeCurrent))

Verify the Results #

When you drop your date field (from your date dimension table) and the measure into a table visual, you'll get exactly the output you want:

Quick Notes #

• Date Dimension Table: It's best practice to use a dedicated date dimension table linked to your UserLogins table (on the date field) to avoid issues with duplicate dates or missing dates in your login data.
• Start Date Filter: The DATE(2021,7,1) clause ensures we don't count any users who might have logged in before our specified start date.

内容的提问来源于stack exchange，提问作者Hessam P.