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
    )
)
SteveHailey's avatar
SteveHailey
Icon for Solution Specialist rankSolution Specialist
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

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

    SteveHailey Thank you so much, dont know why i didnt think of a summarize function. Worked like magic.

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

    SteveHailey ,

     

    Please can i ask one more question? I am trying to "count the emp IDs where the interval is 6 making sure the dates at interval 6 is greater like (effective date at the 6 interval > the effective date at interval 0). Is that something you can help me with.

     

    Here is how i approached it. I created two date measures for both interval 0 and interval 6. I did a if((date_at_interval_6) > (date_at_interval_0) && table[interval] = 6, distinctcount(table[emp_ID]). But i keep getting blank.

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

      Hi yve214. 

      Give this a try:

      Count of Emp IDs = 
COUNTROWS(
    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 again for all your help. I was able to get the same value I got but the dates didnt filter that count as expected.
        I had another measure listed as such:

         

        var _max0 = maxx(filter(allselected(Table), Table[Emp_id] = max(Table[Emp_id]) && Table[Interval] =0),Table[Effective_Date])
        var _max5 = maxx(filter(allselected(Table), Table[Emp_id] = max(Table[Emp_id]) && Table[Interval] =5),Table[Effective_Date])
        return
        calculate(distinctcount(table[emp_ID]), filter(Table, Table[Interval] =5 && _max5>_max0))

         

        which gave me the number but I get that only in a row context, not a card value. What can i change here?