Running rowcount with filter by date

[FabvE]

Hi,

I tried various hints from this forum but found no solution for my problem.

My data is like this:

• table name: Activity
• column Operation: different values like "FileUploaded", "FileDownloaded", "FileAccessed"
• column Date created: datetime when the operation was fullfilled

Date Created	Operation
01.11.2024	FileUploaded
01.11.2024	FileDownloaded
02.11.2024	FIleDownloaded
02.11.2024	FileUploaded
02.11.2024	FileUploaded
02.11.2024	FileUploaded
03.11.2024	FileDownloaded
03.11.2024	FileUploaded
04.11.2024	FileUploaded

What do I try to achieve?

I want a visual to show the rising operations filtered by "FileUploaded" per day.

Based on the example above the data for the visual should look like this

Date	count per day	running count for visual
01.11.2024	1	1
02.11.2024	3	4
03.11.2024	1	5
04.11.2024	1	6

I searched this forum and tried some solutions but nothing worked.

Maybe you have a good solution. 🙂

[Bibiano_Geraldo]

Hi @FabvE ,

1- Create a Measure for Daily Count:

DailyCount =
CALCULATE(
    COUNTROWS('Activity'),
    'Activity'[Operation] = "FileUploaded"
)

2- Create a Measure for Running Total:

RunningCount =
VAR MaxDate = MAX('Activity'[Date Created])
RETURN
SUMX(
    FILTER(
        ALL('Activity'[Date Created]),
        'Activity'[Date Created] <= MaxDate
    ),
    [DailyCount]
)

[FabvE]

Thank you all for your replies, @shafiz_p @Poojara_D12 @Laxmanjatoth .

Unfortunately now I get the error "visual exceeds the available ressources". 😐 My table has +400k rows...

[shafiz_p · Accepted solution]

Try this one :

RunningTotalFileUploaded = 
VAR CurrentDate = MAX(Activity[Date Created])
VAR FilteredTable = 
    FILTER(
        ALL(Activity[Date Created]),
        Activity[Date Created] <= CurrentDate
    )
RETURN
CALCULATE(
    COUNTROWS(Activity),
    FilteredTable,
    Activity[Operation] = "FileUploaded"
)

If above measure still not workable, then try the summarize version given below. First will summarize the activity table by date created and operation and count and then do cummulative sum:

RunningTotalFileUploaded = 
VAR CurrentDate = MAX(Activity[Date Created])

VAR SummaryTable = 
SUMMARIZE(
    FILTER(
        ALL(Activity),
        Activity[Operation] = "FileUploaded"
    ),
    Activity[Date Created],
    Activity[Operation],
    "Count", COUNTROWS(Activity)
)

RETURN
SUMX(
    FILTER(
        SummaryTable,
        [Date Created] <= CurrentDate
    ),
    [Count]
)

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[FabvE]

Wow, thank you. The second one did it!!!!! (although my laptop is burning now 😄 )

[Laxmanjatoth]

will you send example file with 20 rows data ?

[Laxmanjatoth]

Running Total =
CALCULATE(
[Count per Day],
FILTER(
ALL('YourTableName'[Date Created]),
'YourTableName'[Date Created] <= MAX('YourTableName'[Date Created])
)
)

[shafiz_p]

Hi @FabvE  Try this:

RunningTotalFileUploaded = 
CALCULATE(
    COUNTROWS(Activity),
    FILTER(
        ALL(Activity),
        Activity[Date Created] <= MAX(Activity[Date Created]) &&
        Activity[Operation] = "FileUploaded"
    )
)

Output:

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[Poojara_D12]

Hi @FabvE 

To achieve this, you can create two measures:

Count per Day: This will count the occurrences of "FileUploaded" per day.

Count per Day = 
CALCULATE(COUNTROWS(Activity), Activity[Operation] = "FileUploaded")

Running Total: This will calculate the cumulative total of "FileUploaded" operations up to each date.

Running Total = 
CALCULATE(
   SUMX(FILTER(ALL(Activity), Activity[Date Created] <= MAX(Activity[Date Created])), [Count per Day])
)

After creating these measures, you can add them to a line or bar chart with Date Created on the axis, Count per Day for the value, and Running Total for the cumulative count.

This should give you the visual with the "count per day" and the "running count" as you described.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS  (360 remaining for 500 SUBS) Please support!!