Current Month and Previous Month Count of Logins

[pbeeyeqs]

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. 

[Anonymous · Accepted solution]

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]

)

[Anonymous]

[pbeeyeqs]

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+...

[Anonymous · Accepted solution]

Seems you need to add filter for year also.

[pbeeyeqs]

Brilliant. Thank you.

[PattemManohar]

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

[Anonymous · Accepted solution]

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]

)

[pbeeyeqs]

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

[PattemManohar]

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.