topic Re: Can't get the recent Date Values in DAX Commands and Tips

Can't get the recent Date Values

yve214 — Sat, 29 Jan 2022 02:23:09 GMT

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

Re: Can't get the recent Date Values

emjp — Sat, 29 Jan 2022 04:18:56 GMT

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

Re: Can't get the recent Date Values

SteveHailey — Sat, 29 Jan 2022 22:50:55 GMT

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

Re: Can't get the recent Date Values

yve214 — Mon, 31 Jan 2022 06:55:56 GMT

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

Re: Can't get the recent Date Values

yve214 — Mon, 31 Jan 2022 06:56:57 GMT

Thank you emjp , I was able to get it from using a summarize (select statement) function.

Re: Can't get the recent Date Values

yve214 — Tue, 01 Feb 2022 03:51:30 GMT

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.

Re: Can't get the recent Date Values

SteveHailey — Wed, 02 Feb 2022 00:58:25 GMT

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

Re: Can't get the recent Date Values

yve214 — Wed, 02 Feb 2022 13:24:07 GMT

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?

Re: Can't get the recent Date Values

SteveHailey — Wed, 02 Feb 2022 14:29:37 GMT

Hello yve214. You're welcome. Could you give me some more info about what you mean by "the dates didn't filter the account as expected"? Maybe even provide me a .pbix with sample data.

It seems to be working OK in the sample .pbix file that I created here. Perhaps you could take a look at my file also.

Re: Can't get the recent Date Values

yve214 — Wed, 02 Feb 2022 15:13:50 GMT

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

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

Re: Can't get the recent Date Values

SteveHailey — Wed, 02 Feb 2022 16:04:36 GMT

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

Re: Can't get the recent Date Values

yve214 — Wed, 09 Feb 2022 23:09:01 GMT

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