Forum Discussion

yve214's avatar
yve214
Icon for Helper III rankHelper III
4 years ago
Solved

Can't get the recent Date Values

Hi there,   I am trying to get the recent date value for the table below.    I have tried the max and lastdate function but I cant seem to get the results i am expecting.  I was able to get a se...
date filter
Maxdate
  • SteveHailey's avatar
    SteveHailey
    4 years ago

    Hello yve214. 

    I was able to get this to work by creating a new table with the following DAX:

    New table = 
ADDCOLUMNS(
    SUMMARIZE( 'Table', 'Table'[Emp_ID], 'Table'[Interval] ),
    "Effective Date", CALCULATE( MAX( 'Table'[Effective_Date] ) ),
    "Score", CALCULATE( MAX( 'Table'[Score] )),
    "ID", CALCULATE( MAX( 'Table'[ID] ) )
)

     

    I created a .pbix file that you can download here. 


    -Steve

  • SteveHailey's avatar
    SteveHailey
    4 years ago

    Hi yve214. Ah, I think understand now. See if this works for you:

    Count of Emp IDs =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Emp_ID] ),
    FILTER (
        'Table',
        VAR CurrentRowEmpID = 'Table'[Emp_ID]
        RETURN
            VAR IntervalZeroEffectiveDate =
                CALCULATE (
                    MIN ( 'Table'[Effective_Date] ),
                    ALL ( 'Table' ),
                    'Table'[Interval] = 0
                        && 'Table'[Emp_ID] = CurrentRowEmpID
                )
            RETURN
                'Table'[Interval] = 6
                    && 'Table'[Effective_Date] > IntervalZeroEffectiveDate
    )
)
yve214's avatar
yve214
Icon for Helper III rankHelper III
4 years ago

SteveHailey ,

 

Sorry the date filter works for the interval 6 > interval 0.

I was able to add a row 13 for emp ID 2001 to test how it works and I noticed

  1. its counting the correct filtered rows but not the distinct emp ID counts

When i placed the results in a tableview it gave me a context view butI see 2001 twice since it counted the rows rather than the distinct call out. I tried tweaking yours too but I get the same total as if the dates didnt filter.

 

sorry not sure why i cant attach file

 

SteveHailey's avatar
SteveHailey
Icon for Solution Specialist rankSolution Specialist
4 years ago

Hi yve214. Ah, I think understand now. See if this works for you:

Count of Emp IDs =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Emp_ID] ),
    FILTER (
        'Table',
        VAR CurrentRowEmpID = 'Table'[Emp_ID]
        RETURN
            VAR IntervalZeroEffectiveDate =
                CALCULATE (
                    MIN ( 'Table'[Effective_Date] ),
                    ALL ( 'Table' ),
                    'Table'[Interval] = 0
                        && 'Table'[Emp_ID] = CurrentRowEmpID
                )
            RETURN
                'Table'[Interval] = 6
                    && 'Table'[Effective_Date] > IntervalZeroEffectiveDate
    )
)
  • yve214's avatar
    yve214
    Icon for Helper III rankHelper III
    4 years ago

    SteveHailey thank you so much for your help. Everything worked out perfectly.