Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JayZee
New Member

Help With Measure

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

IDEffective DateDepartment
A112/1/2020HR
A111/1/2020HR
A112/2/2019IT
A111/2/2019IT
B110/1/2020Finance
B110/1/2020IT
B110/2/2019IT
B110/2/2019IT
C18/1/2020HR
C18/1/2020Finance
C18/2/2019IT 
C18/2/2019IT
D19/1/2020IT
D19/1/2020IT
D19/2/2019Finance
D19/2/2019HR

 

Expected Results if date seleted is 12/1/20

IDEffective DateDepartment
A112/1/2020HR
B110/1/2020Finance
C18/1/2020HR
D19/1/2020IT

 

Expected Results if date seleted is 12/1/19

IDEffective DateDepartment
A112/1/2019IT
B110/2/2019IT
C18/2/2019IT 
D19/2/2019Finance
1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

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
    )

 

re.png

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.

View solution in original post

3 REPLIES 3
v-yingjl
Community Support
Community Support

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
    )

 

re.png

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
Super User
Super User

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
Super User
Super User

@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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors