CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
KARTHIKEYAN.A
KARTHIKEYAN.A

Reputation: 20088

How to find who logged in yesterday but not today using power bi?

I'm trying to create measure on power bi base on who logged yesterday but not today

My table is

enter image description here

where the count is how many time user logged on that day

How to find the count of following categories using measure on power bi

1) count of Today logged but not yesterday
2) count of Yesterday logged but not today
3) count of who logged yesterday and today as well

Upvotes: 0

Views: 138

Answers (1)

Olly
Olly

Reputation: 7891

Assuming you are trying to count distinct values of Id where Sum of Count matches your measure criteria, then we can summarize the Count per ID for Yesterday and Today, then filter that table based on your criteria, and count rows to return the result:

1) Count of today logged but not yesterday:

Measure1 = 
COUNTROWS ( 
    FILTER ( 
        SUMMARIZECOLUMNS ( 
            MyTable[Id],
            "Count Yesterday",
            CALCULATE ( 
                SUM ( MyTable[Count] ),
                MyTable[Date] = TODAY() - 1
            ),
            "Count Today",
            CALCULATE ( 
                SUM ( MyTable[Count] ),
                MyTable[Date] = TODAY()
            )
        ),
        [Count Yesterday] = 0 && [Count Today] > 0
    )
)

2) Count of yesterday logged but not today:

Measure2 = 
COUNTROWS ( 
    FILTER ( 
        SUMMARIZECOLUMNS ( 
            MyTable[Id],
            "Count Yesterday",
            CALCULATE ( 
                SUM ( MyTable[Count] ),
                MyTable[Date] = TODAY() - 1
            ),
            "Count Today",
            CALCULATE ( 
                SUM ( MyTable[Count] ),
                MyTable[Date] = TODAY()
            )
        ),
        [Count Yesterday] > 0 && [Count Today] = 0
    )
)

3) Count of who logged yesterday and today as well:

Measure3 = 
COUNTROWS ( 
    FILTER ( 
        SUMMARIZECOLUMNS ( 
            MyTable[Id],
            "Count Yesterday",
            CALCULATE ( 
                SUM ( MyTable[Count] ),
                MyTable[Date] = TODAY() - 1
            ),
            "Count Today",
            CALCULATE ( 
                SUM ( MyTable[Count] ),
                MyTable[Date] = TODAY()
            )
        ),
        [Count Yesterday] > 0 && [Count Today] > 0
    )
)

Upvotes: 1

Related Questions