Help With Measure

[JayZee]

Hi there!

I have a dataset and I am trying to get the latest "Department" depending on a date I select in a date slicer. Below is an example of the dataset. The measure would return the value of the Department with the max Effective date that is right before the selected date from the slicer. I can't use just the MAX funtion because it will return the max value no matter what date I select. Please see below for what I am looking for. Thank you in advance for your help! 🙂

Dataset

ID	Effective Date	Department
A1	12/1/2020	HR
A1	11/1/2020	HR
A1	12/2/2019	IT
A1	11/2/2019	IT
B1	10/1/2020	Finance
B1	10/1/2020	IT
B1	10/2/2019	IT
B1	10/2/2019	IT
C1	8/1/2020	HR
C1	8/1/2020	Finance
C1	8/2/2019	IT
C1	8/2/2019	IT
D1	9/1/2020	IT
D1	9/1/2020	IT
D1	9/2/2019	Finance
D1	9/2/2019	HR

Expected Results if date seleted is 12/1/20

ID	Effective Date	Department
A1	12/1/2020	HR
B1	10/1/2020	Finance
C1	8/1/2020	HR
D1	9/1/2020	IT

Expected Results if date seleted is 12/1/19

ID	Effective Date	Department
A1	12/1/2019	IT
B1	10/2/2019	IT
C1	8/2/2019	IT
D1	9/2/2019	Finance

[v-yingjl · Accepted solution]

Hi @JayZee ,

Based on your description, you need to add an index column for each ID in power query first, the query is like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEyMjAy0DfUNzQCsj2ClGJ10MQNMcQNLfWNIOo9QzDFDRHiTsjmGADZbpl5iXnJqdglUTVBDCNC3BnJHAuEW9GFka12RjLKAsMkdGEXJJMsiRUGG2KJZi+6HMipsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Effective Date" = _t, Department = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Effective Date", type date}, {"Department", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Count", each _, type table [ID=nullable text, Effective Date=nullable date, Department=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Effective Date", "Department", "Index"}, {"Custom.Effective Date", "Custom.Department", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom.Effective Date", "Effective Date"}, {"Custom.Department", "Department"}, {"Custom.Index", "Index"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Effective Date", type date}, {"Department", type text}, {"Index", Int64.Type}})
in
    #"Changed Type1"

Close and apply it in power query, create a measure like this, put it in the visual filter and set its value as 1:

Visual control =
VAR _Name =
    CALCULATE (
        MAX ( 'Table'[Department] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID]
                IN DISTINCT ( 'Table'[ID] )
                    && YEAR ( SELECTEDVALUE ( 'Date'[Effective Date] ) )
                        = YEAR ( SELECTEDVALUE ( 'Table'[Effective Date] ) )
                    && 'Table'[Effective Date] <= SELECTEDVALUE ( 'Date'[Effective Date] )
                    && 'Table'[Index]
                        = CALCULATE (
                            MIN ( 'Table'[Index] ),
                            FILTER (
                                ALL ( 'Table' ),
                                'Table'[ID] = EARLIER ( 'Table'[ID] )
                                    && YEAR ( SELECTEDVALUE ( 'Date'[Effective Date] ) )
                                        = YEAR ( SELECTEDVALUE ( 'Table'[Effective Date] ) )
                                    && 'Table'[Effective Date] <= SELECTEDVALUE ( 'Date'[Effective Date] )
                            )
                        )
        )
    )
VAR _Date =
    CALCULATE (
        MAX ( 'Table'[Effective Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID]
                IN DISTINCT ( 'Table'[ID] )
                    && YEAR ( SELECTEDVALUE ( 'Date'[Effective Date] ) )
                        = YEAR ( SELECTEDVALUE ( 'Table'[Effective Date] ) )
                    && 'Table'[Effective Date] <= SELECTEDVALUE ( 'Date'[Effective Date] )
                    && 'Table'[Index]
                        = CALCULATE (
                            MIN ( 'Table'[Index] ),
                            FILTER (
                                ALL ( 'Table' ),
                                'Table'[ID] = EARLIER ( 'Table'[ID] )
                                    && YEAR ( SELECTEDVALUE ( 'Date'[Effective Date] ) )
                                        = YEAR ( SELECTEDVALUE ( 'Table'[Effective Date] ) )
                                    && 'Table'[Effective Date] <= SELECTEDVALUE ( 'Date'[Effective Date] )
                            )
                        )
        )
    )
RETURN
    IF (
        _Name = SELECTEDVALUE ( 'Table'[Department] )
            && _Date = SELECTEDVALUE ( 'Table'[Effective Date] ),
        1,
        0
    )

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[d_gosbell]

Appart from ID A1 every other ID has duplicate Effective Dates which is going to make it impossible to do what you want as 2 departments are effective on the same date, but assuming this is just a typo in your sample data you could do something like the following.

Latest Dept = if (HASONEVALUE('Table'[ID]),

var maxDate = max('Calendar'[Date] )

var maxEffective = CALCULATE(max('Table'[Effective Date]), 'Table'[Effective Date] < maxDate)

var result = CALCULATE(VALUES('Table'[Department]), 'Table'[Effective Date] = maxEffective )

return result)

Note: This assumes you have your date slicer connected to a date table (called 'Calendar' in the example expression) that has no relationships to the main table.

[amitchandak]

@JayZee , Create a new column end date

end date = maxx(filter(table, [id] =earlier([ID]) && [Department] =earlier([Department]) && [Effective Date] <earlier([Effective Date])),[Effective Date])-1

Then try an measure like this with independent date table
measure =
var _max = maxx(allselected('Date'),'Date'[Date])
return
calculate(countrows(table), filter(table, table[Effective Date] <=_max and table[Effective Date] >= _max))

or follow a approch like my HR blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://www.youtube.com/watch?v=e6Y-l_JtCq4