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
Anonymous
Not applicable

Getting percent change with EARLIER

I'm trying to get percent change between data extracts that are not pulled on a regular basis. 


Extract Date     Count of Index     % change

May 14                       13

May 19                       16                   23%

Jun 1                           18                  13%

Jun 2                           9                    50%

 

I also need change in status from one extract to the next:

Status                        May 14     May 19         Jun 1       Jun 2

Approved                     6              6 (0%)       8 (33%)    3 (-63%)

Needs Assignment       2              3 (50%)     5 (66%)    4 (-20%)

Ready                           5              7 (40%)     5 (-29%)   2 (-60%)

 

 

I'm open to whatever will get this done. Here's what I've tried:


Previous Date = CALCULATE (MAX ('Profile Tracking'[Extract Date] ), FILTER ( 'Profile Tracking', 'Profile Tracking'[Extract Date] < EARLIER ( 'Profile Tracking'[Extract Date] ) ) )

 

Previous Count = CALCULATE(COUNT('Profile Tracking'[Index]),'Profile Tracking'[Extract Date]=EARLIER('Profile Tracking'[Previous Date]))

 

This isn't getting me something I can use to figure out the percent change. Help on both of these would be much appreciated.

Thanks!

 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

 

1.jpg

 

 

Create 2 measures

 

Count_Status = COUNT('Table'[Extract Date])
 
Percent Change =
var _a = MAX('Table'[Extract Date])
var _previousdate = CALCULATE(MAX('Table'[Extract Date]), FILTER(ALLEXCEPT('Table','Table'[Status]), 'Table'[Extract Date] < _a))

var _previousvalue = CALCULATE([Count_Status], FILTER(ALLEXCEPT('Table','Table'[Status]), 'Table'[Extract Date] = _previousdate))
RETURN

DIVIDE([Count_Status] - _previousvalue,_previousvalue)
 
 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

View solution in original post

7 REPLIES 7
V-pazhen-msft
Community Support
Community Support

@Anonymous 

For the second question, please provide us a sample data without sensitive data.

 

Regards
Paul 

Anonymous
Not applicable

@V-pazhen-msft Does this work? I'm still trying to figure out what the message board will allow. 🙂 

 

Extract DateStatusIndex
May 14Ready1
May 14Ready2
May 14Ready3
May 14Ready4
May 14Ready5
May 14Needs Assignment6
May 14Needs Assignment7
May 14Approved8
May 14Approved9
May 14Approved10
May 14Approved11
May 14Approved12
May 14Approved13
May 19Ready14
May 19Ready15
May 19Ready16
May 19Ready17
May 19Ready18
May 19Ready19
May 19Ready20
May 19Needs Assignment21
May 19Needs Assignment22
May 19Needs Assignment23
May 19Approved24
May 19Approved25
May 19Approved26
May 19Approved27
May 19Approved28
May 19Approved29
June 1Ready30
June 1Ready31
June 1Ready32
June 1Ready33
June 1Ready34
June 1Needs Assignment35
June 1Needs Assignment36
June 1Needs Assignment37
June 1Needs Assignment38
June 1Needs Assignment39
June 1Approved40
June 1Approved41
June 1Approved42
June 1Approved43
June 1Approved44
June 1Approved45
June 1Approved46
June 1Approved47
June 2Ready48
June 2Ready49
June 2Needs Assignment50
June 2Needs Assignment51
June 2Needs Assignment52
June 2Needs Assignment53
June 2Approved54
June 2Approved55
June 2Approved56

 

What I'm trying to get to is this:

 May 14% changeMay 19% changeJun 1% changeJun 2% change
Approved6 60%833%3-63%
Needs Assignment2 350%567%4-20%
Ready5 740%5-29%2-60%

 

Thank you so much for your help!

Anonymous
Not applicable

Hoping this bumps this up, still can't figure this out.

 

With the data above, I need to figure out percent change with two measures, Status and Extract Date. I can't figure out how to get it to disaggregate to that level.

 

What I'm trying to get to is: on X date, there were 3 items with status of Approved. On X-1 date, there were 8 items. The percent change from X-1 to X is -63%.

Hi @Anonymous ,

 

 

1.jpg

 

 

Create 2 measures

 

Count_Status = COUNT('Table'[Extract Date])
 
Percent Change =
var _a = MAX('Table'[Extract Date])
var _previousdate = CALCULATE(MAX('Table'[Extract Date]), FILTER(ALLEXCEPT('Table','Table'[Status]), 'Table'[Extract Date] < _a))

var _previousvalue = CALCULATE([Count_Status], FILTER(ALLEXCEPT('Table','Table'[Status]), 'Table'[Extract Date] = _previousdate))
RETURN

DIVIDE([Count_Status] - _previousvalue,_previousvalue)
 
 

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Anonymous
Not applicable

@harshnathani  THANK YOU!!!

 

I think the crux of my problem was that I was creating columns instead of measures. Your solution worked perfectly!

HotChilli
Super User
Super User

If you are using columns, the second one will be:   (sub in your table names)

 

Previous Count = CALCULATE(SUM(TableGG[Count of Index]), FILTER(TableGG, TableGG[Extract Date] = EARLIER(TableGG[Previous Date])))

 

and

% will be

 

%change = DIVIDE(TableGG[Count of Index] - TableGG[Previous Count]   , TableGG[Previous Count])

 

These can be combined or made into measures but at least you can see what's going on at each stage

 

Anonymous
Not applicable

Huzzah, we're getting closer! Thanks @HotChilli!

 

Previous Count = DIVIDE((CALCULATE(SUM('Profile Tracking'[Count of Index Col]), FILTER('Profile Tracking', 'Profile Tracking'[Extract Date] = EARLIER('Profile Tracking'[Previous Date])))),'Profile Tracking'[Count of Index Col])

 

Then % change got harder because it kept trying to mix columns and measures. I finally beat it into submission with:

 

Count by Date = 
CALCULATE(
    COUNT('Profile Tracking'[Index]), 
    ALLEXCEPT('Profile Tracking', 'Profile Tracking'[Extract Date])
)

 

and

 

% Change by Date = 
VAR __BASELINE_VALUE = 'Profile Tracking'[Previous Count]
VAR __VALUE_TO_COMPARE = 'Profile Tracking'[Count by Date]
RETURN
	IF(
		NOT ISBLANK(__VALUE_TO_COMPARE),
		DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)
	)

 

 

So I now have % change by date. On to the next problem!

 

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.