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.
Hi All,
I would like to get the value that preceedes based on the earlier date. The value needs to also match the group number.
My table looks like that:
+------------+-------+-------+----------------+ | date | group | value | previous value | +------------+-------+-------+----------------+ | 2019-02-02 | 2 | 50 | 45 | | 2019-02-02 | 1 | 60 | 80 | | 2019-01-18 | 2 | 45 | | | 2019-01-18 | 1 | 80 | |
After looking different ressources online that ressemble my question I came up with the following query which gives me a blank column.:
RateChange = CALCULATE( VALUES(table[Rate]), FILTER( ALL(table), table[date] = EARLIER(table[date])-1 && table[group] = EARLIER(table[group]) ) )
I need help in getting the query to work as calculated column and measure.
Thank you !
Solved! Go to Solution.
hi, @RogerSteinberg
Is this the case that there are two or more value for one group on the one date,
if so the previous value should be sum/ max/ min value?
You could also try this way:
Step1:
Add a group rank column for each group by date
Group rank = RANKX(FILTER('table','table'[group]=EARLIER('table'[group])),'table'[date],,ASC,Dense)
Step2:
Use this formula to get the result
Result = CALCULATE ( SUM ( 'table'[value] ), FILTER ( 'table', 'table'[group] = EARLIER ( 'table'[group] ) && 'table'[Group rank] = EARLIER ( 'table'[Group rank] ) - 1 ) )
Result:
Best Regards,
Lin
hi, @RogerSteinberg
Is this the case that there are two or more value for one group on the one date,
if so the previous value should be sum/ max/ min value?
You could also try this way:
Step1:
Add a group rank column for each group by date
Group rank = RANKX(FILTER('table','table'[group]=EARLIER('table'[group])),'table'[date],,ASC,Dense)
Step2:
Use this formula to get the result
Result = CALCULATE ( SUM ( 'table'[value] ), FILTER ( 'table', 'table'[group] = EARLIER ( 'table'[group] ) && 'table'[Group rank] = EARLIER ( 'table'[Group rank] ) - 1 ) )
Result:
Best Regards,
Lin
Try this pattern
= MINX ( TOPN ( 1, FILTER ( 'Table', [group] = EARLIER ( [group] ) && [date] < EARLIER ( [date] ) ), [date], DESC ), [Rate] )
Hi Muhammad, thank you for this suggestion - it worked brilliantly for what I needed it for - finding the most recent value by concatenating several criteria into a single index column - i.e. the "group" in this example.
This formula found the most recent value, I also need to find the value closest to the date 1 week prior. Any ideas / suggestions on how I'd do that?
Thanks
Ben
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 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |