Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

DAX code for today's table data

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

Need Help
Show and Tell
Tips and Tricks
  • PaulDBrown's avatar
    PaulDBrown
    3 years ago

    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]
    )

     

5 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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=117875464156957854959&rtpof=true&sd=true

       

      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's avatar
        PaulDBrown
        Community Champion
        3 years ago

        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's avatar
    PaulDBrown
    Community Champion
    3 years ago

    Try:

    Calculated table =
SUMMARIZE (
    FILTER (
        'Logged Clockings',
        'Logged Clockings'[Touch Description] <> "Shift End"
    ),
    'Logged Clockings'[Date],
    'Logged Clockings'[Employee Clock Number]
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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!