Forum Discussion
Add column with same calculation
neeHi
I hope some body can help me!
I have 2 tables, one with dates and one with values, they are connected.
I then what to calculate the sum of the last month and insert the value in a column and same value for every row.
So my data could look something like:
| Date | Value |
| 2020-01-01 | 5 |
| 2020-01-10 | 8 |
| 2020-01-15 | 88 |
| 2020-01-20 | 9 |
| 2020-01-30 | 55 |
| 2020-02-08 | 654 |
| 2020-02-18 | 85 |
| 2020-02-25 | 1 |
The sum of the last month is 740 I would then like to have a new column there is just 740 for every row. Like below
| Date | Value | Total |
| 2020-01-01 | 5 | 740 |
| 2020-01-10 | 8 | 740 |
| 2020-01-15 | 88 | 740 |
| 2020-01-20 | 9 | 740 |
| 2020-01-30 | 55 | 740 |
| 2020-02-08 | 654 | 740 |
| 2020-02-18 | 85 | 740 |
| 2020-02-25 | 1 | 740 |
Hope some body can help me 🙂
@Snip , Create a new column as
Column = sumx(FILTER('Table',EOMONTH([date],0) =EOMONTH(max([Date]),0)),[Value])Hi Anonymous ,
Please try this:
Measure = VAR mon_ = CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) ) RETURN CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Date' ), 'Date'[Month] = MONTH ( mon_ ) && MAX ( 'Table'[Value] ) <> BLANK () ) )
3 Replies
@Snip , Create a new column as
Column = sumx(FILTER('Table',EOMONTH([date],0) =EOMONTH(max([Date]),0)),[Value])Hi Anonymous ,
Please try this:
Measure = VAR mon_ = CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) ) RETURN CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALL ( 'Date' ), 'Date'[Month] = MONTH ( mon_ ) && MAX ( 'Table'[Value] ) <> BLANK () ) )