Aggregation

Question

Hi all,&nbsp;&nbsp;I have an issue with my data source and I need to be able to report on data in the charts and tables - basically front end.&nbsp;I need to go from the current state:Current StateDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Column1&nbsp; Column231/12/2018&nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 012/12/2018&nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp;to :Desired StateDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Column1&nbsp; Column231/12/2018&nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 112/12/2018&nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp;&nbsp;How to do it best&nbsp; - front end vs back endMany thanks,&nbsp;&nbsp;&nbsp;

vanessafvg · Answer

Anonymous&nbsp; not entirely sure what you asking but i think you could probably do a switch statement
https://docs.microsoft.com/en-us/dax/switch-function-dax
&nbsp;
create a calculated column
&nbsp;
desired&nbsp;state&nbsp;column1&nbsp;=SWITCH&nbsp;(&nbsp;[column1],&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2,&nbsp;5,&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3,&nbsp;3&nbsp;)
&nbsp;
&nbsp;

v-frfei-msft · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
To create two calculated columns as below.
&nbsp;
Desired 1 = CALCULATE(SUM('current'[column1]),FILTER('current','current'[date]&lt;=EARLIER('current'[date])))
Desired 2 = CALCULATE(SUM('current'[column2]),FILTER('current','current'[date]&lt;=EARLIER('current'[date])))

&nbsp;
Regards,
Frank

anonymous · Answer

HI&nbsp;v-frfei-msft&nbsp;&nbsp;thank you for your input. FYI my actual table has more records than 2 as per my example, I am risking to state the obvious. :)&nbsp;The adoption of your suggested solution&nbsp; - FMVaggr = CALCULATE(SUM('CurrentT4'[FMV]),FILTER('CurrentT4','CurrentT4'[GL Date]&lt;=EARLIER('CurrentT4'[GL Date])))&nbsp;error message - "A sigle value for column "GL Date"&nbsp; in table "CurrentT4" cannot be determined. This can happen when a measure&nbsp; formula refers to column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."&nbsp;

anonymous · Answer

hi&nbsp;vanessafvgI need to be able to aggregate data&nbsp; as the dates progress.&nbsp;Thanks

anonymous · Answer

I would really appreciate if anyone could help me to solve this.&nbsp;Many thanks!Anonymous&nbsp;wrote:Hi all,&nbsp;&nbsp;I have an issue with my data source and I need to be able to report on data in the charts and tables - basically front end.&nbsp;I need to go from the current state:Current StateDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Column1&nbsp; Column231/12/2018&nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 012/12/2018&nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp;to :Desired StateDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Column1&nbsp; Column231/12/2018&nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 112/12/2018&nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp;&nbsp;How to do it best&nbsp; - front end vs back endMany thanks,&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

Aggregation

9 Replies

Related content

Data Aggregation issue

Aggregate

Average monthly sales buy aggregated

LBL or Aggregation Upstream

Using Aggregate Tables and Partitions