Cummulative Average in Line Chart

[HeihoSilver]

Hi, 
I need to establishe a line chart where it accumulate the average value of a Days Open vs Month Year for the last 12 complete months.

I have many Datasets to connect to but to simplify I need to use Table1 and Date Table (standard date table using CALENDERAUTO function). 
The data is unfortunately available in direct connection so I'm not able to play around with the table, but have to use measures. 

Here is the simple data:
Table1

Case Number	Days Open	Created
Case111	352	7/18/2023 0:00
Case 222	337	8/2/2023 0:00
Case 333	330	8/9/2023 0:00
Case 444	316	8/23/2023 0:00
Case 555	260	10/18/2023 0:00
Case 666	255	10/23/2023 0:00
Case 777	226	11/21/2023 0:00
Case 888	218	11/29/2023 0:00
Case 999	218	11/29/2023 0:00
Case 1000	198	12/19/2023 0:00

The Cummulative average data result should be like this

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

So I tried to play around using direct drag and drop with Average of Days Open (Y axis) and Date in Date Table column (X axis) and here is the result. The value is completely different than the result table above.

So, I play around with measures code:

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]),
        'Active RITM Delivery Time'[Created] <= MAX('DateTable'[Date])
    )
)

And here is the result.

The problem with the above code seems to not include all data, for example in November 29th, there are 2 data with 218 days open (Case 888 and Case 999). However, the Cumulative Number in November from the chart (286.75) seems to only count the cumulative average on first data (just the first 218, not count all on the same date). Therefore, the data after November will also show incorrect value. 

Can anyone please advice how to achieve the correct value?
Thanks

[HeihoSilver · Accepted solution]

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

[Anonymous]

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

[Anonymous]

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

[HeihoSilver]

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

[Sergii24]

Hi @HeihoSilver, there are few steps you'd need to perform:

• create a column with End of Month date - we'll use it to group the dates by month

End of Month = EOMONTH( 'Table'[Created], 0 )

• Create the following measure:

Days Open Cummulative Avg = 
VAR _MaxSelectedMonth = MAX( 'Table'[End of Month] )        //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] 
    )

In this measure we collect the max of currenlty selected month, then replace the filter context and finally calculate the average.

I hope that it's what you were looking for 🙂 Good luck with you project!

P. S. pbix is attached, feel free to contact me in case you need more clarificaionts!