DAX Measure - Latest Record for each key within dynamic time window

[LoganGalindoWSP]

Hello!

I am trying to create a measure that will supply me with a single number.

That number will represent the count of items in my inventory that meet the following criteria:

 - Has the status condition "Good" within snapshot A

 - Has the status condition "Fair" within snapshot B

Each Snapshot is a unique view of the entire inventory, with the following restrictions:

- Limited to only one record for each item (key); this should be the record with the latest Date.

 - Only includes records (rows) with dates that are on or before the Date chosen via a Slicer.

 - Only includes records (rows) with dates that are within a 2 years and 1 month window before the Date chosen via a Slicer. Same slicer as the above point.

The goal of this Dax Measure is to allow me to display this count, via Cards Visuals, over a premade background that tracks each unqiue way an item's condition can change. This count only cares about the items that started at condition "Good" and ended at condition "Fair". I hope to be able to use the code provided to help me build the other measures for the other calculations (I.e. Good - Good, Fair - Good, Fair - Fair, etc)

This 'change' is between two points in time, that are defined by the user. The user picks a date (from a before date slicer) for both "sides" / Snapshots of the comparison. The code should be able to grab this chosen date and remove everything that is older than [This Date] minus 2 years. For example, if the user selected 07/01/2024; The Snapshot only looks at the items that has any condition betwen the timeframe of 07/01/2022 - 07/01/2024, Then it grabs only the Latest Record for each item (key).

Example Inventory Table:

User Selected Dates and Example Data tables:

Measure Expected output:   2
(count of items that started at Good and ended at Fair)

I have an Inventory with the following columns:

 - Key / ID

 - Condition

 - Record Date

In Power Query, I am pulling in a duplicate of this inventory. This allows me to assign each thier own before date slicer to help user selection. So I really have two tables: Inventory Snapshot A & Inventory Snapshot B. I also have a lookup table that connects both of these tables on the Key column.

How would you write a Dax Measure to accomplish this count?

Thanks for the help!

[v-shex-msft]

Hi @LoganGalindoWSP ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[talespin · Accepted solution]

hi @LoganGalindoWSP 

I am somewhat confused with the requirement of date filter "Only includes records (rows) with dates that are less than 2 years and 1 month before the Date chosen via a Slicer. Same slicer as the above point."

What I have done is Whatever date is selected in slicer, table will only include records less than or equal to slicer date and greate than equal to slicer date - 2years, from that it will show latest record for each Key.

Sharing file as well

https://drive.google.com/file/d/1V1OI3RMVtRu3dxBtpd74xDxBrW671vh7/view?usp=sharing

Step1 :

I have used three tables, One Inventory table and two disconnected date tables for slicers.

This is the Datamodel

Step2:

Created first two measures and applied filter on corresponding table visuals.

Created 3rd Measure and used it in card for count. This will only count Good-Fair, you can change it accordingly. Please test it thoroughly.

---------------------------------------------------------------------------------

SnapshotA =

VAR _SelDate = SELECTEDVALUE(SnapshotA[Date])

VAR _DtMinus2Yrs = DATE( YEAR(_SelDate)-2, MONTH(_SelDate), DAY(_SelDate) )

VAR _FilterTbl = FILTER( ALL(Inventory), Inventory[Date] >= _DtMinus2Yrs && Inventory[Date] <= _SelDate)

RETURN RANK(DENSE, _FilterTbl, ORDERBY([Date], DESC), PARTITIONBY([Key]))

---------------------------------------------------------------------------------

SnapshotB =

VAR _SelDate = SELECTEDVALUE(SnapshotB[Date])

VAR _DtMinus2Yrs = DATE( YEAR(_SelDate)-2, MONTH(_SelDate), DAY(_SelDate) )

VAR _FilterTbl = FILTER( ALL(Inventory), Inventory[Date] >= _DtMinus2Yrs && Inventory[Date] <= _SelDate)

RETURN RANK(DENSE, _FilterTbl, ORDERBY([Date], DESC), PARTITIONBY([Key]))

---------------------------------------------------------------------------------

Count GF =

VAR _SelDateSSA = SELECTEDVALUE(SnapshotA[Date])

VAR _DtMinus2YrsSSA = DATE( YEAR(_SelDateSSA)-2, MONTH(_SelDateSSA), DAY(_SelDateSSA) )

VAR _FilterTblSSA = FILTER( ALL(Inventory), Inventory[Date] >= _DtMinus2YrsSSA && Inventory[Date] <= _SelDateSSA)

VAR _SummTblSSA = ADDCOLUMNS(

                                _FilterTblSSA,

                                "@SSARank",

                                RANK(DENSE, _FilterTblSSA, ORDERBY([Date], DESC), PARTITIONBY([Key]))

                            )

VAR _FltrTblSSA2 = FILTER(_SummTblSSA, [@SSARank] = 1)

VAR _SelDateSSB = SELECTEDVALUE(SnapshotB[Date])

VAR _DtMinus2YrsSSB = DATE( YEAR(_SelDateSSB)-2, MONTH(_SelDateSSB), DAY(_SelDateSSB) )

VAR _FilterTblSSB = FILTER( ALL(Inventory), Inventory[Date] >= _DtMinus2YrsSSB && Inventory[Date] <= _SelDateSSB)

VAR _SummTblSSB = ADDCOLUMNS(

                                _FilterTblSSB,

                                "@SSBRank",

                                RANK(DENSE, _FilterTblSSB, ORDERBY([Date], DESC), PARTITIONBY([Key]))

                            )

VAR _FltrTblSSB2 = FILTER(_SummTblSSB, [@SSBRank] = 1)

RETURN SUMX(

                _FltrTblSSA2,

                VAR _Key = [Key]

                RETURN IF(

                            [Condition] = "Good" && SUMX( FILTER(_FltrTblSSB2, [Key] = _Key), IF([Condition] = "Fair", 1, 0)) = 1,

                            1,

                            0

                )

)

---------------------------------------------------------------------------------

[LoganGalindoWSP]

Thank you @talespin.

After a good bit of testing, I can confirm this solution is exactly what I was looking for. Thanks again!

[talespin]

hi @LoganGalindoWSP 

You're welcome.

[talespin]

hi @LoganGalindoWSP 

I understood the Snapshot A and SnapshotB(Assuming you have two date slicers filtering two saparate visuals SnapshotA and SnapshotB).

Can you please describe in detail what is it that you expect in the output.

I want to count the records in my inventory that have BOTH 

 - status condition "Good" during snapshot A

 - status condition "Fair" during snapshot B

[LoganGalindoWSP]

Hi @talespin ,

Thank you for your message.

I have updated the original post with more information.