Forum Discussion

FabvE's avatar
FabvE
Helper I
1 year ago
Solved

Running rowcount with filter by date

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 CreatedOperation
01.11.2024FileUploaded
01.11.2024FileDownloaded
02.11.2024FIleDownloaded
02.11.2024FileUploaded
02.11.2024FileUploaded
02.11.2024FileUploaded
03.11.2024FileDownloaded
03.11.2024FileUploaded
04.11.2024FileUploaded

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

Datecount per dayrunning count for visual
01.11.202411
02.11.202434
03.11.202415
04.11.20241

6

 

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

 

Maybe you have a good solution. 🙂

Need Help
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    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

8 Replies

    • shafiz_p's avatar
      shafiz_p
      Super User
      1 year ago

      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's avatar
        FabvE
        Helper I
        1 year ago

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

  • Poojara_D12's avatar
    Poojara_D12
    Super User
    1 year ago

    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!!

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    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

  • Laxmanjatoth's avatar
    Laxmanjatoth
    Resolver I
    1 year ago

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





  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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]
)