Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
Hi @Anonymous ,
Create 2 measures
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
@Anonymous
For the second question, please provide us a sample data without sensitive data.
Regards
Paul
@V-pazhen-msft Does this work? I'm still trying to figure out what the message board will allow. 🙂
Extract Date | Status | Index |
May 14 | Ready | 1 |
May 14 | Ready | 2 |
May 14 | Ready | 3 |
May 14 | Ready | 4 |
May 14 | Ready | 5 |
May 14 | Needs Assignment | 6 |
May 14 | Needs Assignment | 7 |
May 14 | Approved | 8 |
May 14 | Approved | 9 |
May 14 | Approved | 10 |
May 14 | Approved | 11 |
May 14 | Approved | 12 |
May 14 | Approved | 13 |
May 19 | Ready | 14 |
May 19 | Ready | 15 |
May 19 | Ready | 16 |
May 19 | Ready | 17 |
May 19 | Ready | 18 |
May 19 | Ready | 19 |
May 19 | Ready | 20 |
May 19 | Needs Assignment | 21 |
May 19 | Needs Assignment | 22 |
May 19 | Needs Assignment | 23 |
May 19 | Approved | 24 |
May 19 | Approved | 25 |
May 19 | Approved | 26 |
May 19 | Approved | 27 |
May 19 | Approved | 28 |
May 19 | Approved | 29 |
June 1 | Ready | 30 |
June 1 | Ready | 31 |
June 1 | Ready | 32 |
June 1 | Ready | 33 |
June 1 | Ready | 34 |
June 1 | Needs Assignment | 35 |
June 1 | Needs Assignment | 36 |
June 1 | Needs Assignment | 37 |
June 1 | Needs Assignment | 38 |
June 1 | Needs Assignment | 39 |
June 1 | Approved | 40 |
June 1 | Approved | 41 |
June 1 | Approved | 42 |
June 1 | Approved | 43 |
June 1 | Approved | 44 |
June 1 | Approved | 45 |
June 1 | Approved | 46 |
June 1 | Approved | 47 |
June 2 | Ready | 48 |
June 2 | Ready | 49 |
June 2 | Needs Assignment | 50 |
June 2 | Needs Assignment | 51 |
June 2 | Needs Assignment | 52 |
June 2 | Needs Assignment | 53 |
June 2 | Approved | 54 |
June 2 | Approved | 55 |
June 2 | Approved | 56 |
What I'm trying to get to is this:
May 14 | % change | May 19 | % change | Jun 1 | % change | Jun 2 | % change | |
Approved | 6 | 6 | 0% | 8 | 33% | 3 | -63% | |
Needs Assignment | 2 | 3 | 50% | 5 | 67% | 4 | -20% | |
Ready | 5 | 7 | 40% | 5 | -29% | 2 | -60% |
Thank you so much for your help!
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 ,
Create 2 measures
Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
@harshnathani THANK YOU!!!
I think the crux of my problem was that I was creating columns instead of measures. Your solution worked perfectly!
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
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!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |