Running Count

[maf]

I've titled this Running Count rather than Running Total as I've seen plenty of examples that sum numerics to a given date.

I'd like to count rows to a date rather than sum values.

A good example would be Installs.  A row with a timestamp is added to the Installs table everytime someone installs an app (suprisingly).  So...

Mon 6 installs

Tue  10 installs

Wed 20 installs

would give Installs to date as...

Mon 6

Tues 16

Wed 36

Any ideas ?

[TomMartens]

Hey,

here is a little example

On the page YTD Variations you will find this measure

Cumulated Count Rows = 
CALCULATE(
 COUNTROWS('FactWithDates'),
 FILTER(
  ALL('Calendar'),
  'Calendar'[Date] <= MAX('FactWithDates'[Date]) &&
  'Calendar'[Year] = MAX('Calendar'[Year])
 )

 Here just the visible rows in the current FilterContext (introduced using CALCULATE()) are counted

Hope this helps

[maf]

Appologies for my ignorance but I don't see any running count on that page.

[TomMartens]

Now it's there again, somehow I managed to not use the measure in the table viz

[maf]

Still can't see it

[TomMartens]

It seems there have been some upload issues, please try again

[maf]

I was being stupid !  Couldn't understand the numbers until I noticed that you were counting rows by year aswell, so it didn't look like I expected.  All is good now apart from when I try to apply it to my data I get..

"Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models."

Any ideas ?

[v-yulgu-msft]

Hi @maf,

Since you were fetching data in Direct Query mode, you should create a measure rather than calculated column. The error was prompted because you created a column using above formula in Direct Query mode.

Regards,
Yuliana Gu

[maf]

Can you explain what I should put in the messure to get the same results please ?

[v-yulgu-msft]

Hi @maf,

You can refer to the DAX formula provided in TomMartens's first post.

Regards,
Yuliana Gu

[maf]

I've tried using that in a measure but get

Function 'FILTER' is not supported in this context in DirectQuery mode. 😞

[TomMartens]

Hey,

check the following option

and turn it on, please be aware, that some calculations can slow down your query performance.