Forum Discussion

JohanKnape's avatar
JohanKnape
Regular Visitor
9 years ago

Total count that month

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?

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      JohanKnape
      Regular Visitor
      9 years ago

      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.