Disctinct count in daterange with visual calculations

[somogyitamas88]

Hi all!

I have an activity table with dates and user's operations. I need to calculate for a line chart (with timeline) a number for each date with unique user id-s on the previous 7 days.

I've created a DAX function over the datamodel with window function, but it's too slow. So I would like to replace it with a visual calculation formula where I put the date and userid fields to the line chart from the model.

What should be the formula then?

[v-echaithra]

Hi @somogyitamas88 ,

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[v-echaithra]

Hi @somogyitamas88 ,

Thank you @FBergamaschi , @Kedar_Pande  for your inputs.

I just wanted to check if the issue has been resolved on your end, or if you require any further assistance. Please feel free to let us know, we’re happy to help!

Thank you 

[Kedar_Pande]

@somogyitamas88 

7D Unique Users = 
RANKX(
WINDOW(-6,0,ALLSELECTED(Date[Date]),ORDERBY(Date[Date])),
DISTINCTCOUNT(Activity[UserID]),
,
ASC
)

Date (X-axis), UserID (small multiples or legend), measure above (Y)

[FBergamaschi]

Hi @somogyitamas88,

I see the code was alreayd given to you so I will not go on that

My worry is that you realize than the visual calculation will give a different result than a measure on the first 6 days if you apply a filter to the dates in the report

Imagine you filter a single month, say November 2025. On 1st of nov 205 I assume you would like to see a number depending on the last 7 days of october. But that, with a visual calculation, is impossible

Just wanted you to be aware of this

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[somogyitamas88]

Thank you.

[cengizhanarslan]

Instead of window function could you try the formula below:

Active Users (7D) =
VAR EndDate = MAX ( 'DimDate'[Date] )
RETURN
CALCULATE(
    DISTINCTCOUNT ( Activity[UserId] ),
    DATESINPERIOD ( 'DimDate'[Date], EndDate, -7, DAY )
)

[somogyitamas88]

Unfortunately it's too slow.

This is my current one:

NumOfUsers7day_timeline =

CALCULATE(

    [NumOfUsers],

    WINDOW(

        -6, 0,

        REL,

        ORDERBY('Date'[Dates], ASC)

    )

)

It's faster however with more 10-15 sec loading.

[MasonMA]

Hi, 

You can use this below for visual calculation.

test =

    CALCULATE(

    SUM([UserID]),

    WINDOW(

        -6, 0,

        ORDERBY([Activity Date]

    )

    )

    )

[deborshi_nag]

Hi @somogyitamas88

Solution: For each date on the X‑axis, count the unique User IDs over the previous 7 calendar days (including that date).

 

- Put Date on the line chart X‑axis (continuous, daily).

- Drag UserId into the visual so that it’s available to the calculation without cluttering the chart.

- With the visual selected, choose Home ▸ New calculation to open the Visual Calculation editor. 

 

Use the following visual calculation formula -

 

Active Users (7D, VC) =

COUNTROWS(

    DISTINCT(

        SELECTCOLUMNS(

            WINDOW( -6, 0, ORDERBY([Date], ASC) ),

            "U", [UserId]

        )

    )

)

 

Hope this helps - please appreciate by leaving a Kudos or accepting it as a Solution to help others! 

[somogyitamas88]

Hi, it only shows the distinct num of dates in the previous 7 days. (Mainly show 7, but when there are no activity on a day, its less).

[deborshi_nag]

Hi  @somogyitamas88

The visual calculation is operating on one row per Date, so the WINDOW is returning 7 date rows, and the DISTINCT(...) is effectively counting dates—not users.

 

Try the following change:

• X‑axis: put your Date (continuous, daily).

• Y‑axis: any metric (you can even use a dummy measure like 1).

• Details: add UserId (this is the key step).

  • If the chart explodes into too many series, don’t worry—Power BI’s line chart won’t draw a separate line per detail value; Details just increases row-granularity for the calc.

  • With the visual selected, Home ▸ New calculation and paste:

    Active Users (7D, VC) =

    COUNTROWS(

        DISTINCT(

            SELECTCOLUMNS(

                WINDOW( -6, 0, ORDERBY([Date], ASC) ),

                "U", [UserId]

            )

        )

    )

[somogyitamas88]

For me Details accepts only measure (at least implicit one). So I can drag User ID nowhere.