cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
yve214
Helper II
Helper II

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:

IDEmp_IDIntervalEffective_DateScore
11001001/01/20203.1

2

1001008/26/20203.3
31001311/20/20203.5
41001505/05/20203.2
5100152/1/20214.1
6100201/1/20222.0

 

Expected result if i want to see the recent date values if i filter by interval 0.

 

IDEmp_IDEffective_DateScore
210018/26/2020

3.3

610021/1/20222.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.

 

IDEmp_IDIntervalEffective_DateScore
2100108/26/2020

3.3

6100201/1/20222.0
31001311/20/20203.5
5100152/1/20214.1

 

2 ACCEPTED SOLUTIONS
SteveHailey
Solution Specialist
Solution Specialist

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

 

SteveHailey_0-1643496452836.png

SteveHailey_1-1643496479458.png

SteveHailey_2-1643496498025.png

I created a .pbix file that you can download here


-Steve

View solution in original post

SteveHailey
Solution Specialist
Solution Specialist

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

View solution in original post

11 REPLIES 11
SteveHailey
Solution Specialist
Solution Specialist

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

 

SteveHailey_0-1643496452836.png

SteveHailey_1-1643496479458.png

SteveHailey_2-1643496498025.png

I created a .pbix file that you can download here


-Steve

@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
Solution Specialist
Solution Specialist

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

@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?

SteveHailey
Solution Specialist
Solution Specialist

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.

@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

yve214_0-1643814738485.png

 

SteveHailey
Solution Specialist
Solution Specialist

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 thank you so much for your help. Everything worked out perfectly.

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

emjp
Frequent Visitor

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. 

 

emjp_3-1643429136865.png

...

emjp_4-1643429181184.png

 

emjp_5-1643429274161.png

 

my Power BI Version, without a DAX Expresion

 

emjp_6-1643429415714.png

 

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

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors