Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |