DAX code for today's table data

[Anonymous]

Hi all,

I have the following DAX code:

Calculated table =
VAR __Filter =
CALCULATETABLE (
VALUES ( 'Logged Clockings'[Employee Clock Number] ),
'Logged Clockings'[Touch Description] = "Shift End"
)
VAR __Result =
FILTER (
SUMMARIZECOLUMNS (
'Logged Clockings'[Date],
'Logged Clockings'[Employee Clock Number]
),
NOT ( 'Logged Clockings'[Employee Clock Number] ) IN __Filter
)
RETURN
__Result

I need this code to run for the last date in 'Logged Clockings' table (which is essentially today, or tomorrow if I view the table tomorrow). Any help is appreciated!

I tried using LASTDATE in the code above but did not work.

Thanks in advance!

Midhun

[PaulDBrown]

In that case try:

Calculated table =
SUMMARIZE (
    FILTER (
        'Logged Clockings',
        'Logged Clockings'[Touch Description] <> "Shift End"
            && 'Logged Clockings'[Date] = TODAY ()
    ),
    'Logged Clockings'[Date],
    'Logged Clockings'[Employee Clock Number]
)

[Anonymous]

Hi @PaulDBrown ,

I tried your DAX solution (instead of mine which is inefficient by the way), and there is a slight change in the output. Before I explain let me give you a bit of context by giving you a sample dataset:

https://docs.google.com/spreadsheets/d/1JCT11VFbF0z3nD7Iy1OpEu0hphVFCoYN/edit?usp=share_link&ouid=11...

The FILTER() in your DAX code means now I have rows for "Shift Start", "Break Start" and "Break End"  corresponding to an employee. So my final output will also have the "Employee Clock Number" of someone who clocked out of a shift, i.e., has a "Shift End" row corresponding to the clock number.

It's better understood when you try it one the sample data!

Thanks for your reply!

Midhun

[PaulDBrown · Accepted solution]

Thanks for the sample data. See if this has better performance:

Today Table =
VAR _SE =
    CALCULATETABLE (
        VALUES ( 'Logged Clockings'[Employee Clock Number] ),
        FILTER (
            'Logged Clockings',
            'Logged Clockings'[Touch Description] = "Shift End"
                && 'Logged Clockings'[Date] = TODAY ()
        )
    )
VAR _ACN =
    CALCULATETABLE (
        VALUES ( 'Logged Clockings'[Employee Clock Number] ),
        FILTER ( 'Logged Clockings', 'Logged Clockings'[Date] = TODAY () )
    )
VAR _Net =
    EXCEPT ( _ACN, _SE )
RETURN
    SUMMARIZE (
        FILTER (
            'Logged Clockings',
            'Logged Clockings'[Date] = TODAY ()
                && 'Logged Clockings'[Employee Clock Number] IN _Net
        ),
        'Logged Clockings'[Date],
        'Logged Clockings'[Employee Clock Number]
    )

[PaulDBrown]

Try:

Calculated table =
SUMMARIZE (
    FILTER (
        'Logged Clockings',
        'Logged Clockings'[Touch Description] <> "Shift End"
    ),
    'Logged Clockings'[Date],
    'Logged Clockings'[Employee Clock Number]
)

[Anonymous]

Hi Paul, 

Thanks for replying. What I meant was:

The CALCULATETABLE function refers to value in 'Logged Clockings' and I only want the rows that correspond to today's date from Logged Clockings.

I have a solution but I don't think it's the most efficient. I created another calculated column:

Today =

FILTER('Logged Clockings', 'Logged Clockings'[Date] = TODAY())

And then my final CALCULATETABLE refers to 'Today' table, as follows:

Live Clocked In =

VAR __Filter =

    CALCULATETABLE (

        VALUES ( 'Today'[Employee Clock Number] ),

        'Today'[Touch Description] = "Shift End"

    )

VAR __Result =

    FILTER (

        SUMMARIZECOLUMNS (

            'Today'[Date],

            'Today'[Employee Clock Number]

        ),

        NOT ( 'Today'[Employee Clock Number] ) IN __Filter

    )

RETURN

    __Result

Thanks!