Compare Daily values for Entity and agency

[Anonymous]

Hi everyone,

I'm facing an issue in term of performance, I have a custom connector which returns a rating for agencies, country and type of rate (Short tem, long term,....)
It returns 7000 lines every day, but I'm facing an issue, this is a sample of my dataset

Agency	Nature	Entity	Date (DD/MM/YYYY)	Rating
Fitch	LT	Poland	01/01/2024	8
Fitch	LT	France	01/01/2024	4
Fitch	LT	Germany	01/01/2024	7
Fitch	ST	Poland	01/01/2024	6
Fitch	ST	France	01/01/2024	4
Fitch	ST	Germany	01/01/2024	7
Fitch	LT	Poland	01/01/2024	8
Fitch	LT	France	01/01/2024	4
Fitch	LT	Germany	01/01/2024	6
Fitch	ST	Poland	01/01/2024	6
Fitch	ST	France	01/01/2024	4
Fitch	ST	Germany	01/01/2024	7

What I need, to achieve, is to compare the rate for a date and the day before
So to do that, I used the following approach
1. Create a column which is combinaison of Agency, Nature & entity
2. Sort the table by Combinaison & Date
3. Add to index one from 0 and one from 1

4. Make a self join between index0 and Index1

After that, I tret the issue with dax or some basic command in Power Query

Do you know how can I improve the performance of this case? because a refresh on Power BI services took 1h30 when it did not fail....

[danextian · Accepted solution]

Hi @Anonymous 

Instead of doing this in M, I would use  a DAX measure. You can use PREVIOUSDAY() to access the value the previous day.

Value Previous Day = 
CALCULATE ( SUM ( 'Table'[Rating] ), PREVIOUSDAY ( 'Table'[Date] ) )

Note: I am not sure what aggregation to use (SUM, MAX, AVERAGE). Your sample data has multiple rows for the same Agency, Nature, Entity and Date combinations.

[rajendraongole1]

Hi @Anonymous -Your current approach (creating combination keys, sorting, indexing, and self-joining) is functional but not optimal for large datasets. The long refresh time in Power BI Service is likely due to inefficient data processing, especially in Power Query (M language). Use Power Query Self-Join Method for Maximum Performance. if you can share table or reference will check.

if duplicates exist, clean up.Avoids Indexing & Sorting (which are costly operations).Efficient Merge using a date-based self-join.Power Query handles joins faster than DAX in large datasets.

Hope the above suggestion helps.

[Anonymous]

So my second approach, was to create a unique identifier:
By converting date in int and having for example:
KeyDate

FLT_FRANCE45778 (01/05/2025 convert into int)
Then I create KeyDatePrevious (so on the same line, I will have:
FLT_FRANCE45777 (30/04/2025 convert into int)

Then I make a self join on KeyDate and KeyDatePrevious

Will this approach get better results ?
I made a test but just with close and apply, and I did not see a significatif enhancement