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

Current Month and Previous Month Count of Logins

I've got 4 tables:

 

1) COMPANY (Company ID, Company Name, ...)

2) EMPLOYEE (Employee ID, Company ID, Employee Name, ...)

3) LOGINS (Login ID, Employee ID, Login Date, ...)

4) DATES TABLE

 

I want to show the total count of Logins for COMPANY for Current month and previous month to get below:

 

-----------------------------------------------------------------------

| COMPANY NAME   | CURRENT MONTH  | PREVIOUS MONTH  |
| Company A             | xxx                           |  yyy                          |

| Company B             | aaa                           |  bbb                         |

| ...                             | ...                              |  ...                             |

----------------------------------------------------------------------

 

I am not sure what measures to create. Nothing seems to be working. 

 

Thanks in advance. 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
ramsyBI Regular Visitor
Regular Visitor

Re: Current Month and Previous Month Count of Logins

data.PNG

 

You need to have the relationship in star schema and apply below measure:

 

Total Count last Month =
countx(
FILTER(
Login,
MONTH(Login[Date])=MONTH(TODAY())-1
),
Login[LoginID]
)
 
 
Total Count This Month =
countx(
FILTER(
Login,
MONTH(Login[Date])=MONTH(TODAY())
),
Login[LoginID]
)

View solution in original post

ramsyBI Regular Visitor
Regular Visitor

Re: Current Month and Previous Month Count of Logins

Seems you need to add filter for year also.

 

Capture.PNG

View solution in original post

8 REPLIES 8
Super User
Super User

Re: Current Month and Previous Month Count of Logins

It will be great if you can provide the sample data for each table which will save time in creating test data and enables to focus on the logic bit.


Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





ramsyBI Regular Visitor
Regular Visitor

Re: Current Month and Previous Month Count of Logins

data.PNG

 

You need to have the relationship in star schema and apply below measure:

 

Total Count last Month =
countx(
FILTER(
Login,
MONTH(Login[Date])=MONTH(TODAY())-1
),
Login[LoginID]
)
 
 
Total Count This Month =
countx(
FILTER(
Login,
MONTH(Login[Date])=MONTH(TODAY())
),
Login[LoginID]
)

View solution in original post

ramsyBI Regular Visitor
Regular Visitor

Re: Current Month and Previous Month Count of Logins

Capture.PNG

pbeeyeqs Regular Visitor
Regular Visitor

Re: Current Month and Previous Month Count of Logins

I have the schema like below. I will build now and see how it goes. 

 

schema.png

pbeeyeqs Regular Visitor
Regular Visitor

Re: Current Month and Previous Month Count of Logins

This appears to work IF I have a DATE FILTER for the previous 11 months only. 

 

Otherwise, it appears to be totalling months from previous years. ex: CURRENT MONTH = Aug 2019+Aug2018+...

 

 

 

ramsyBI Regular Visitor
Regular Visitor

Re: Current Month and Previous Month Count of Logins

Seems you need to add filter for year also.

 

Capture.PNG

View solution in original post

pbeeyeqs Regular Visitor
Regular Visitor

Re: Current Month and Previous Month Count of Logins

Brilliant. Thank you.

Super User
Super User

Re: Current Month and Previous Month Count of Logins

Just wandering, what happens for LastMonth count in January month ? Need to handle using PREVIOUSMONTH function.


Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)