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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Abbasi
Frequent Visitor

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

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

 

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Picture1.png

 

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"
)
)
 
 
 
 
 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


@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 IDDate Sale  Sales Agent Commission
A14/01/2020 1000 Maria 10
A15/01/2020 900 Dave 15
A18/01/2020 1100 Andy 20
A21/01/2020 1200 Ali 30
B15/01/2020 2000 Kate 40
B19/01/2020 3000 Rob 50
C14/01/2020 5500 Peter 60
D24/01/2020 7000 Mike 70

 

Desired Results 1    
Store IDSales total by DateOne :Sales total by DateTwo : Agent by Date OneAgent by Date TwoCommission by Date OneCommission  by Date Two
A9001100DaveAndy1520
B20003000KateRob4050
C55005000PeterPeter6060
DNANANANANANA

 

Desired Results 2 Use Only Date One Filter  
Store ID Sales total by DateOne :  Agent by Date One Commission by Date One
A900Dave15
B2000Kate40
C5500Peter60
DNANANA

 

Greg_Deckler
Super User
Super User

@Abbasi 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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.