Compare with previous months with criteria

[Anonymous]

Hi All,

I have 3 columns

1. Employee ID

2. Time Found :- Values( Yes, No)

3. Date

I would like to get a count of employees whose current month Time Found is No when compared the same employees whose Time Found value was Yes last month.

Hopefully i was able to explain my requirement

Let me try an example

Employee ID, Time Found, Date

11 , No,  08/01/2018

12,  Yes,  08/01/2018

13,  No,  08/01/2018

11,  Yes,  07/01/2018

12 , Yes,  07/01/2018

13,  Yes,  07/01/2018

My Count would be 2, since employee id 11 & 13 Time Found is No this month and Yes for last month

Thanks for your help in advance

Regards,

Sandeep

[MattAllington]

You need to understand Time Intelligence in Power BI. My article will help you.

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try this MEASURE as well

Measure =
VAR Currentmonth =MONTH ( MAX ( Table1[ Date] ) )
VAR Currentyear =YEAR ( MAX ( Table1[ Date] ) )
VAR EmployeesCurrentMonth =
    CALCULATETABLE (
        VALUES ( Table1[Employee ID] ),
        MONTH ( Table1[ Date] ) = Currentmonth
            && YEAR ( Table1[ Date] ) = Currentyear,
        Table1[ Time Found] = "No"
    )
VAR EmployeesPreviousmonth =
    CALCULATETABLE (
        VALUES ( Table1[Employee ID] ),
        MONTH ( Table1[ Date] )
            = Currentmonth - 1
            && YEAR ( Table1[ Date] ) = Currentyear,
        Table1[ Time Found] = "Yes"
    )
RETURN
    CONCATENATEX (
        INTERSECT ( EmployeesCurrentMonth, EmployeesPreviousmonth ),
        [Employee ID],
        ", "
    )