cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JohanKnape
Regular Visitor

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 2
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.

 

 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors