Forum Discussion
Running rowcount with filter by date
- 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
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...
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
- FabvE1 year agoHelper I
Wow, thank you. The second one did it!!!!! (although my laptop is burning now 😄 )