Forum Discussion
Difference between values in a single column based on an identifying value
Hi there community! I'm working with a dataset in which I'm trying to display on Customer and Article level the sales Amount. This is done by looking at a set of seperate Excel files all with the...
Hi Anonymous ,
Please try:
% (Customer level) = var _nextDate= MINX(FILTER(ALL('Table'),[Customer]=MAX('Table'[Customer])&& [File Date]>MAX('Table'[File Date])),[File Date]) var _diff=CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'), [Customer]=MAX('Table'[Customer]) &&[File Date]=_nextDate ))-SUM('Table'[Amount]) return DIVIDE(_diff,SUM('Table'[Amount]))Or
% (Article level) = var _nextDate= MINX(FILTER(ALL('Table'),[Customer]=MAX('Table'[Customer])&&[Article]=MAX('Table'[Article]) && [File Date]>MAX('Table'[File Date])),[File Date]) var _nextSum=CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'), [Customer]=MAX('Table'[Customer])&&[Article]=MAX('Table'[Article])&&[File Date]=_nextDate )) var _current=CALCULATE(SUM('Table'[Amount]),FILTER('Table', [Customer]=MAX('Table'[Customer])&&[Article]=MAX('Table'[Article]))) return DIVIDE( _nextSum-_current, _current)Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Please try:
% (Customer level) =
var _nextDate= MINX(FILTER(ALL('Table'),[Customer]=MAX('Table'[Customer])&& [File Date]>MAX('Table'[File Date])),[File Date])
var _diff=CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'), [Customer]=MAX('Table'[Customer]) &&[File Date]=_nextDate ))-SUM('Table'[Amount])
return DIVIDE(_diff,SUM('Table'[Amount]))
Or
% (Article level) =
var _nextDate= MINX(FILTER(ALL('Table'),[Customer]=MAX('Table'[Customer])&&[Article]=MAX('Table'[Article]) && [File Date]>MAX('Table'[File Date])),[File Date])
var _nextSum=CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'), [Customer]=MAX('Table'[Customer])&&[Article]=MAX('Table'[Article])&&[File Date]=_nextDate ))
var _current=CALCULATE(SUM('Table'[Amount]),FILTER('Table', [Customer]=MAX('Table'[Customer])&&[Article]=MAX('Table'[Article])))
return DIVIDE( _nextSum-_current, _current)
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.