Aggregation

[fortune318]

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,

[fortune318]

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

Many thanks!

---

@fortune318 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]

Hi @fortune318 ,

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

[fortune318]

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]

@fortune318  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 )

[fortune318]

hi @vanessafvg

---

---

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

Thanks

[v-frfei-msft]

Hi @fortune318 ,

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]))

[fortune318]

Hi @v-frfei-msft ,

Many thanks for your suggestions.

I'll try to create a calculated table if that does not work - I'll share file with you.

Many thanks!

[fortune318]

Hi     @v-frfei-msft 

I tried as you suggested but it does not work I get error.

How to add the file so you could have a look , sorry I have never done that before.

Thanks

[fortune318]

Hi @v-frfei-msft ,

Many thanks for your suggestions.

I'll try to create a calculated table if that does not work - I'll share file with you.

Many thanks!