Forum Discussion
Solved
Cumulative line values for chart
Hello, I would like to build cumulative line value for below chart. I have plan for all month for example its 2400min in December I have 18 working days so 2400/18 = 133,33 daily. I w...
HI ToKo ,
Did your table contain the original amount field? I'd like to suggest you use the following measure formula with original amount fields:
Measure = VAR currDate = MAX ( 'Table'[Date] ) VAR dateRange = CALCULATETABLE ( VALUES ( 'Table'[Date] ), FILTER ( ALLSELECTED ( 'Table' ), YEAR ( [Date] ) = YEAR ( currDate ) && MONTH ( [Date] ) = MONTH ( currDate ) ) ) VAR total = CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( ALLSELECTED ( 'Table' ), YEAR ( [Date] ) = YEAR ( currDate ) && MONTH ( [Date] ) = MONTH ( currDate ) ) ) VAR duration = DATEDIFF ( MINX ( dateRange, [Date] ), MAXX ( dateRange, [Date] ), DAY ) RETURN total / duration * DATEDIFF ( MINX ( dateRange, [Date] ), currDate, DAY )Regards,
Xiaoxin Sheng
parry2k I have one table with cumulative sum for each line and AvrageForLine for this i would like to build cumulative avrage line for chart.
| Line | Date | CumulativeSum | AvrageForLine |
| 1111 | 2019-12-01 | 34 | 28 |
| 1112 | 2019-12-01 | 50 | 44 |
| 1113 | 2019-12-01 | 70 | 65 |
| 1111 | 2019-12-02 | 50 | 28 |
| 1112 | 2019-12-02 | 80 | 44 |
| 1113 | 2019-12-02 | 100 | 65 |
| 1111 | 2019-12-03 | 80 | 28 |
| 1112 | 2019-12-03 | 120 | 44 |
| 1113 | 2019-12-03 | 150 | 65 |
| 1111 | 2019-12-04 | 110 | 28 |
| 1112 | 2019-12-04 | 160 | 44 |
| 1113 | 2019-12-04 | 190 | 65 |
HI ToKo ,
Did your table contain the original amount field? I'd like to suggest you use the following measure formula with original amount fields:
Measure = VAR currDate = MAX ( 'Table'[Date] ) VAR dateRange = CALCULATETABLE ( VALUES ( 'Table'[Date] ), FILTER ( ALLSELECTED ( 'Table' ), YEAR ( [Date] ) = YEAR ( currDate ) && MONTH ( [Date] ) = MONTH ( currDate ) ) ) VAR total = CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( ALLSELECTED ( 'Table' ), YEAR ( [Date] ) = YEAR ( currDate ) && MONTH ( [Date] ) = MONTH ( currDate ) ) ) VAR duration = DATEDIFF ( MINX ( dateRange, [Date] ), MAXX ( dateRange, [Date] ), DAY ) RETURN total / duration * DATEDIFF ( MINX ( dateRange, [Date] ), currDate, DAY )Regards,
Xiaoxin Sheng