cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

COUNT Rows based on dates

hi all

 

i have a report on when developers complete training modules. each developer is assigned 4 modules to complete and an assigned date/time is applied to the relevant dev row

 

the table looks like the below

the Module columns are date/time

 

DeveloperModule 1Module 2Module 3Module 4
Dev 1  05/08/2019 
Dev 2 12/08/201905/08/2019  
Dev 3 09/08/2019  

 

what i am trying to achieve is a trend chart that shows the accumalated count of developers per module

 

my problem is i am unable to get the accumulated total over time but rather gives me the total completed on a specific date. also if the date/time stamp happens to be the exactly the same it isnt a distinct count

 

im not sure what i am doing wrong that is unable to determine the accmulated/trend count

 

any help would be appreciated

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: COUNT Rows based on dates

Hello @PowerBi_Batman 

This measure uses a date table and to get the running total target.

Module 1 RT = 
VAR EndingDate = LASTDATE(Dates[Date])
RETURN
IF ( EndingDate <= EOMONTH ( TODAY() ),
    CALCULATE(COUNTROWS(YourTable),YourTable[Module 1] <= EndingDate, NOT ISBLANK(YourTable[Module 1]))  
)

There are four of these measures, one for each module.  I uploaded my sample file here ModuleRT.pbix

ModuleRTImage.jpg

View solution in original post

4 REPLIES 4
Super User
Super User

Re: COUNT Rows based on dates

Hello @PowerBi_Batman 

This measure uses a date table and to get the running total target.

Module 1 RT = 
VAR EndingDate = LASTDATE(Dates[Date])
RETURN
IF ( EndingDate <= EOMONTH ( TODAY() ),
    CALCULATE(COUNTROWS(YourTable),YourTable[Module 1] <= EndingDate, NOT ISBLANK(YourTable[Module 1]))  
)

There are four of these measures, one for each module.  I uploaded my sample file here ModuleRT.pbix

ModuleRTImage.jpg

View solution in original post

Re: COUNT Rows based on dates

@jdbuchanan71 
thankyou for your effort.....that works great

 

is there a way to show the current count of this month? as August figures wont appear

 



Super User
Super User

Re: COUNT Rows based on dates

Hi @PowerBi_Batman 

Sorry, I should have done the active month check against the end of the month list this.

Module 1 RT = 
VAR EndingDate = LASTDATE(Dates[Date])
RETURN
IF ( EndingDate <= EOMONTH ( TODAY(), 0 ),
    CALCULATE(COUNTROWS(YourTable),YourTable[Module 1] <= EndingDate, NOT ISBLANK(YourTable[Module 1]))  
)

The 4th line changed to go through the end of the current month.  You would just change that in each of the measures.

Re: COUNT Rows based on dates

@jdbuchanan71 

no apology needed

 

that is perfect.

thank you for your time. it is very much appreciated

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 81 members 1,373 guests
Please welcome our newest community members: