Forum Discussion
DAX Measure - Latest Record for each key within dynamic time window
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))---------------------------------------------------------------------------------
Solution Sage
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
