Forum Discussion

HeihoSilver's avatar
HeihoSilver
Frequent Visitor
2 years ago
Solved

Cummulative Average in Line Chart

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 ...
Need Help
  • HeihoSilver's avatar
    HeihoSilver
    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-YearDays OpenCumulative Average
    Jul-23352352
    Aug-23(352 + 337 + 330 + 316) / 4337.75
    Sep-23(352 + 337 + 330 + 316) / 4337.75
    Oct-23(352 + 337 + 330 + 316 + 260 + 255) / 6308.33
    Nov-23(352 + 337 + 330 + 316 + 260 + 255 + 226 + 218 + 218) / 9279.11
    Dec-23(352 + 337 + 330 + 316 + 260 + 255 + 226 + 218 + 218 + 198) / 10 271

     

     

     

Sergii24's avatar
Sergii24
Super User
2 years ago

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!