Forum Discussion
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 seperate latest effective date column with Measure = Calculate(MAX(Table(Effective_Date), Allexcept(Table, table(id))
Table:
| ID | Emp_ID | Interval | Effective_Date | Score |
| 1 | 1001 | 0 | 01/01/2020 | 3.1 |
2 | 1001 | 0 | 08/26/2020 | 3.3 |
| 3 | 1001 | 3 | 11/20/2020 | 3.5 |
| 4 | 1001 | 5 | 05/05/2020 | 3.2 |
| 5 | 1001 | 5 | 2/1/2021 | 4.1 |
| 6 | 1002 | 0 | 1/1/2022 | 2.0 |
Expected result if i want to see the recent date values if i filter by interval 0.
| ID | Emp_ID | Effective_Date | Score |
| 2 | 1001 | 8/26/2020 | 3.3 |
| 6 | 1002 | 1/1/2022 | 2.0 |
or if i dont filter any of the intervals i should get all client IDs with a recent date value witht their corresponding intervals.
| ID | Emp_ID | Interval | Effective_Date | Score |
| 2 | 1001 | 0 | 8/26/2020 | 3.3 |
| 6 | 1002 | 0 | 1/1/2022 | 2.0 |
| 3 | 1001 | 3 | 11/20/2020 | 3.5 |
| 5 | 1001 | 5 | 2/1/2021 | 4.1 |
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.
-SteveHi 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 ) )
11 Replies
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.
-SteveSteveHailey Thank you so much, dont know why i didnt think of a summarize function. Worked like magic.
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.
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 ) )
I am not sure if Power BI is the best choice for this task. The result can be easily achieved in excel with a slicer.
...
my Power BI Version, without a DAX Expresion
