Forum Discussion
Cummulative Average in Line Chart
- 2 years ago
Hi,
Thank you for your support. I found the way to plot the line chart correctly from Youtube Power BI Cumulative Lince Chart. We just need to add ALLSELECTED in the Calculate filter.CumulAverageDaysOpen_RITMActive = // the formula calculate cumulative Average Days Open for Active RITM for the last 12 complete months. VAR CurrentDate = TODAY() VAR LastFullMonthEnd = EOMONTH(CurrentDate, -1) -- End of the last full month VAR StartDate = EOMONTH(LastFullMonthEnd, -12) -- Start date, 12 months before the end of the last full month -- Table with all dates within the range VAR DatesInRange = FILTER( ALL('DateTable'), 'DateTable'[Date] > StartDate && 'DateTable'[Date] <= LastFullMonthEnd ) -- Cumulative average calculation RETURN AVERAGEX( FILTER( DatesInRange, 'DateTable'[Date] <= MAX('DateTable'[Date]) ), CALCULATE( AVERAGE('Active RITM Delivery Time'[Days Open]), ALLSELECTED('Active RITM Delivery Time'), 'Active RITM Delivery Time'[Created] <= MAX('DateTable'[Date]) ) )Month-Year Days Open Cumulative Average Jul-23 352 352 Aug-23 (352 + 337 + 330 + 316) / 4 337.75 Sep-23 (352 + 337 + 330 + 316) / 4 337.75 Oct-23 (352 + 337 + 330 + 316 + 260 + 255) / 6 308.33 Nov-23 (352 + 337 + 330 + 316 + 260 + 255 + 226 + 218 + 218) / 9 279.11 Dec-23 (352 + 337 + 330 + 316 + 260 + 255 + 226 + 218 + 218 + 198) / 10 271
Hi,
Thank you for your support. I found the way to plot the line chart correctly from Youtube Power BI Cumulative Lince Chart. We just need to add ALLSELECTED in the Calculate filter.
CumulAverageDaysOpen_RITMActive = // the formula calculate cumulative Average Days Open for Active RITM for the last 12 complete months.
VAR CurrentDate = TODAY()
VAR LastFullMonthEnd = EOMONTH(CurrentDate, -1) -- End of the last full month
VAR StartDate = EOMONTH(LastFullMonthEnd, -12) -- Start date, 12 months before the end of the last full month
-- Table with all dates within the range
VAR DatesInRange =
FILTER(
ALL('DateTable'),
'DateTable'[Date] > StartDate &&
'DateTable'[Date] <= LastFullMonthEnd
)
-- Cumulative average calculation
RETURN
AVERAGEX(
FILTER(
DatesInRange,
'DateTable'[Date] <= MAX('DateTable'[Date])
),
CALCULATE(
AVERAGE('Active RITM Delivery Time'[Days Open]),
ALLSELECTED('Active RITM Delivery Time'),
'Active RITM Delivery Time'[Created] <= MAX('DateTable'[Date])
)
)
| Month-Year | Days Open | Cumulative Average |
| Jul-23 | 352 | 352 |
| Aug-23 | (352 + 337 + 330 + 316) / 4 | 337.75 |
| Sep-23 | (352 + 337 + 330 + 316) / 4 | 337.75 |
| Oct-23 | (352 + 337 + 330 + 316 + 260 + 255) / 6 | 308.33 |
| Nov-23 | (352 + 337 + 330 + 316 + 260 + 255 + 226 + 218 + 218) / 9 | 279.11 |
| Dec-23 | (352 + 337 + 330 + 316 + 260 + 255 + 226 + 218 + 218 + 198) / 10 | 271 |
- Anonymous2 years agoNot applicable
Hi,HeihoSilver .
I'm glad to see that you solved the problem and that you shared the correct solution, you can accept the suggestions that you think will help you as solution, so that it can help more people in the forum who have the similar problem with you, so that they can find the useful suggestions faster!
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian