Power BI running total with counting rows by date order

[Anonymous]

I have this file which contains participant ID's in column A and their registration date in column B in 'Sheet1', ordered in random date order. I want to make a graph in power BI that shows the cumulative number of registrations on a certain day. I've looked to previous posts, but don't really know where to start (except that I probably need the count rows function). Could someone help me out?

[Anonymous]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. In Power Query -- Add Column – Index Column – From 1.

2. Create measure.

Measure =
SUMX(
    FILTER(ALLSELECTED('Table'),
    'Table'[registration date]=MAX('Table'[registration date])&&'Table'[Index]<=MAX('Table'[Index])),[Value])

 3. Result:

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi Liu,

Thanks for your response! I tried your method, but it unfortunately did not give the desired outcome. I attached my simplified data source and pbix file in a wetransfer link here. My final goal is to create a linegraph that shows the total number of registrations up until a specific day. (For example if over the past week each day 3 persons registered, on day 7 the graph shows 7 x 3 = 21 registrations.

[Anonymous · Accepted solution]

UPDATE

I fixed it with creating a measure with the following code

Registrations =

CALCULATE (COUNTROWS('Data'),

    FILTER (

        ALL ('Data'),

        'Data'[Registration date] <= MAX('Data'[Registration date])

    )

)