Forum Discussion
Cummulative Average in Line Chart
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,HeihoSilver I am glad to help you.
Hello,Sergii24 ,thanks for your concern about this issue.
Your answer is excellent!And I would like to make some small improvements on the solution you provided.
I've noticed that in Direct Query connection mode, it's a better choice to create measure rather than calculated columns, since it's not recommended to create calculated columns in direct query connection mode
Here's what I've done to improve it, and this dax code returns the original result properly.
measure:
M_eofMon = EOMONTH(MAX('Table'[Created]),0)M_result =
VAR _MaxSelectedMonth = [M_eofMon] //get the currently selected maximum
VAR _TableWithEndOfMonthUntilSelected = //update current filter context (i.e. at every row of a visual, the table is filtered for a single month end, we need to replace it with all dates until the selected one
CALCULATETABLE(
'Table',
'Table'[End of Month] <= _MaxSelectedMonth
)
RETURN
//get the average from virtual table
AVERAGEX(
_TableWithEndOfMonthUntilSelected,
[Days Open]
)
The end result is the same.
If you were able to solve your problem or get ideas for a solution from it, please mark Sergii24 's reply as SOLUTION as it will help other customers in the forum, thanks!
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
Thank you for your swift reply and suggested solution Sergii24 and Anonymous .
I'm not able to create new columns or table due to Direct Query connection. The only way I can play around is to use measures. Hence, new column 'End of Month' cannot be used in my case.
However, I tried your method in other separate pbix file. The cumulative average result presented in the table is correct. However, when I plot to the line chart (which is the required one) it is averaged monthly, the same way as drag and drop method.
What I need is to present the cumulative average rolling on each month for the last 12 complete months in the line chart.
Have you tried plot in the line chart and see if the result is correct (maybe I miss something).
Here is the result of the comparisons (drag and drop, the 2 suggested method, and my previous method with incorrect value).