Total count that month

[JohanKnape]

I'm trying to get a total count of all the rows and show the increase or decline each month. Tried some of the examples here with running totals etc but can't make it work.

I don't have a sum, but it's the amount of total rows that have a date greater than the last month I want to show.

So I have 100k rows. With a date, a guid, productgroup, name etc.

So I want to show the following in a graph

2016-01 -- 100k rows

2016-02 -- 99k rows

2016-03 -- 98k rows

2016-04 -- 99k rows

etc etc.

Anyone got any suggestions? And have I explained properly or do you need more info?

[v-shex-msft]

Hi @JohanKnape,

According to your description, you want to get the rows count of current month, right?

If this is a case, you can try to use below formula if it suitable for your requirement.

Measures:

Count Of Current Month = 
var currDate=MAX([Date])
Return
COUNTX(FILTER(ALL('Date Table'),YEAR([Date])=YEAR(currDate)&&MONTH([Date])=MONTH(currDate)),[Date])


Count Of Previous Month = 
var currDate=MAX([Date])
Return
COUNTX(FILTER(ALL('Date Table'),[Date]>=DATE(YEAR(currDate),MONTH(currDate)-1,1)&&[Date]<=DATE(YEAR(currDate),MONTH(currDate),1)-1),[Date])

Increase=[Count Of Current Month] - [Count Of Previous Month]

Regards,

Xiaoxin Sheng

[JohanKnape]

Hi, did a fast try of your examples but didn't get it to work.

Will try again this afternoon when I have more time and hopefully get it right.