Comparing two records in the same table, on selected dates, using DAX.

[Anonymous]

Looking for a suggestion on how best to compare two records in the same table using DAX?

If record does not exit on the specified date then the previous record, by date, is selected for the comparison.

Given below is an example comparing sale on two different dates in the same table.

User selects 16/01/2020 and 19/01/2020 from two date slicers respectively for comparison.

If the record does not exist on the date then value from previous date is considered

Store ID	Date	Sale
A	14/01/2020	1000
A	15/01/2020	900
A	18/01/2020	1100
A	21/01/2020	1200
B	15/01/2020	2000
B	19/01/2020	3000
C	14/01/2020	5500
D	24/01/2020	7000

I’m looking for the following results

	First Sale Date Selected from Slicer 16/01/2020	Second Sale Date Selected from Slicer 19/01/2020
A	900	1100
B	2000	3000
C	5500	NA
D	NA	NA

[Jihwan_Kim]

Date Two Slicer condition : =

IF (

SELECTEDVALUE ( Dates_Two[Date_Two] ) > SELECTEDVALUE ( Dates_One[Date_One] ),

1,

0

)

Sales total by DateOne : =

VAR _currentstore =

MAX ( Stores[Store ID] )

VAR _selecteddate =

SELECTEDVALUE ( Dates_One[Date_One] )

VAR _maxdate =

CALCULATE (

MAX ( Sales[Date] ),

FILTER (

ALL ( Sales ),

Sales[Store ID] = _currentstore

&& Sales[Date] <= _selecteddate

)

)

RETURN

IF (

HASONEVALUE ( Stores[Store ID] ),

COALESCE (

CALCULATE (

SUM ( Sales[Sale] ),

FILTER (

ALL ( Sales ),

Sales[Store ID] = _currentstore

&& Sales[Date] = _maxdate

)

),

"NA"

)

)

Sales total by DateTwo : =

VAR _currentstore =

MAX ( Stores[Store ID] )

VAR _selecteddate =

SELECTEDVALUE ( Dates_Two[Date_Two] )

VAR _selecteddateDateOne =

SELECTEDVALUE ( Dates_One[Date_One] )

VAR _maxdate =

CALCULATE (

MAX ( Sales[Date] ),

FILTER (

ALL ( Sales ),

Sales[Store ID] = _currentstore

&& Sales[Date] <= _selecteddate

)

)

RETURN

IF (

HASONEVALUE ( Stores[Store ID] ),

COALESCE (

CALCULATE (

SUM ( Sales[Sale] ),

FILTER (

ALL ( Sales ),

Sales[Store ID] = _currentstore

&& Sales[Date] = _maxdate

&& Sales[Date] > _selecteddateDateOne

)

),

"NA"

)

)

Link to the sample pbix file 

[Anonymous]

@Jihwan_Kim Thanks for the detailed input. This is very close to my requirement.
However, I have around 20 more fields to show from the Sales table displaying only one row for each Store as shown before. Here is more detailed example of source and desired output 

Source Data:
Store ID	Date	Sale	Sales Agent	Commission
A	14/01/2020	1000	Maria	10
A	15/01/2020	900	Dave	15
A	18/01/2020	1100	Andy	20
A	21/01/2020	1200	Ali	30
B	15/01/2020	2000	Kate	40
B	19/01/2020	3000	Rob	50
C	14/01/2020	5500	Peter	60
D	24/01/2020	7000	Mike	70

Desired Results 1
Store ID	Sales total by DateOne :	Sales total by DateTwo :	Agent by Date One	Agent by Date Two	Commission by Date One	Commission  by Date Two
A	900	1100	Dave	Andy	15	20
B	2000	3000	Kate	Rob	40	50
C	5500	5000	Peter	Peter	60	60
D	NA	NA	NA	NA	NA	NA

Desired Results 2 Use Only Date One Filter
Store ID	Sales total by DateOne :	Agent by Date One	Commission by Date One
A	900	Dave	15
B	2000	Kate	40
C	5500	Peter	60
D	NA	NA	NA

[Greg_Deckler]

@Anonymous I would use disconnected date tables for your slicers. You can get the values in these using SELECTEDVALUE. Then you would FILTER the table based upon this date and anything less than that date. You would then use MAXX to get the max date in the table and then MAXX and FILTER again to get the Value at that date. Good general example is Lookup Min/Max.  https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

[Anonymous]

@Greg_Deckler I have updated the required output above. Thanks for your feedback