Re: Aggregation

Anonymous · ‎03-28-2019

Hi all,

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.

I need to go from the current state:

Current State

Date Column1 Column2

31/12/2018 2 0

12/12/2018 3 1

to :

Desired State

Date Column1 Column2

31/12/2018 5 1

12/12/2018 3 1

How to do it best - front end vs back end

Many thanks,

Anonymous · ‎04-01-2019

I would really appreciate if anyone could help me to solve this.

Many thanks!

@Anonymous wrote:
Hi all,

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.

I need to go from the current state:
Current State
Date Column1 Column2
31/12/2018 2 0
12/12/2018 3 1

to :
Desired State
Date Column1 Column2
31/12/2018 5 1
12/12/2018 3 1

How to do it best - front end vs back end
Many thanks,

v-frfei-msft · ‎03-28-2019

Hi @Anonymous ,

To create two calculated columns as below.

Desired 1 = CALCULATE(SUM('current'[column1]),FILTER('current','current'[date]<=EARLIER('current'[date])))

Desired 2 = CALCULATE(SUM('current'[column2]),FILTER('current','current'[date]<=EARLIER('current'[date])))

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Anonymous · ‎03-29-2019

HI @v-frfei-msft

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. 🙂

The adoption of your suggested solution - FMVaggr = CALCULATE(SUM('CurrentT4'[FMV]),FILTER('CurrentT4','CurrentT4'[GL Date]<=EARLIER('CurrentT4'[GL Date])))

error message - "A sigle value for column "GL Date" in table "CurrentT4" cannot be determined. This can happen when a measure formula refers to column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

vanessafvg · ‎03-28-2019

@Anonymous 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

create a calculated column

desired state column1 =
SWITCH ( [column1],

2, 5,

3, 3 )

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Anonymous · ‎03-29-2019

hi @vanessafvg

I need to be able to aggregate data as the dates progress.

Thanks

v-frfei-msft · ‎04-01-2019

Hi @Anonymous ,

Could you please share your sample data or Pbix to me via One Drive, kindly upload the files and share the link here.

BTW, maybe your can try to create a calculated table of an Aggregation one by using SUMMARIZECOLUMNS function as below.

Table = SUMMARIZECOLUMNS('current'[Date],"co1",SUM(current[column1]),"co2",SUM(current[column2]))

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.