Forum Discussion

kl8818's avatar
kl8818
Frequent Visitor
2 years ago
Solved

Change Formula

Could someone help me update this formula: Count employee with 2 or more login: = VAR _t =     ADDCOLUMNS (         VALUES ( 'User Logins'[Employee ID] ),         "@logincount", CALCULAT...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

    Count Expected result measure: = 
VAR _today =
    TODAY ()
VAR _lastmonthenddate =
    EOMONTH ( _today, -1 )
VAR _twomonthsbeforestartdate =
    EOMONTH ( _today, -3 ) + 1
VAR _monthtable =
    FILTER (
        ADDCOLUMNS (
            'Users Logins',
            "@year-month", EOMONTH ( 'Users Logins'[Logged In], 0 )
        ),
        [@year-month] >= _twomonthsbeforestartdate
            && [@year-month] <= _lastmonthenddate
    )
VAR _summarytable =
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( _monthtable, [@year-month], 'Users Logins'[Employee ID] ),
            "@logincount", CALCULATE ( COUNTROWS ( 'Users Logins' ) )
        ),
        [@logincount] >= 1
    )
VAR _idlist =
    SUMMARIZE (
        FILTER (
            ADDCOLUMNS (
                _summarytable,
                "@condition",
                    COUNTROWS (
                        FILTER (
                            _summarytable,
                            'Users Logins'[Employee ID] = EARLIER ( 'Users Logins'[Employee ID] )
                        )
                    )
            ),
            [@condition] >= 2
        ),
        'Users Logins'[Employee ID]
    )
RETURN
    COUNTROWS ( FILTER ( _monthtable, 'Users Logins'[Employee ID] IN _idlist ) )
kl8818's avatar
kl8818
Frequent Visitor
2 years ago

I am still having difficulty working with this solution. It does not represent a correct count in my visual with the formula above. 

A sample visual:

I'd like the highlighted part to be a card visual of the total for those that have logged in the previous month and the month prior to previous month. 

This a measurement I created but I do not know how to adjust it to include those two elements.

Current Month Logins =
CALCULATE(
    DISTINCTCOUNT('User Logins'[Employee ID]),
    MONTH('User Logins'[Logged In Date])=MONTH(TODAY()),
    YEAR('User Logins'[Logged In Date])=YEAR(TODAY())
    )