topic Re: Line Chart Legend Rolling Average in DAX Commands and Tips

Line Chart Legend Rolling Average

vjr37 — Thu, 03 Oct 2024 18:16:44 GMT

Have this table of data in power billing with thus measure. Issue I'm running into is when I create a line chart the rolling average is calculated correctly (dates on x axis rolling on your axis). If I add [Name] to legend the names A and B show up on the legend but there remains only 1 line with the total rolling average and not two lines with rolling average for both A and B. Any help would be appreciated

RollingMean6Months =

VAR CurrentDate = MAX('Charge'[Date])

VAR SixMonthsAgo = EDATE(CurrentDate, -6)

RETURN

AVERAGEX(

FILTER(

ALL('Charge'),

'Charge'[Date] <= CurrentDate && 'Charge'[Date] > SixMonthsAgo

'Charge'[Value]

)

Re: Line Chart Legend Rolling Average

Anonymous — Fri, 04 Oct 2024 02:42:54 GMT

Hi vjr37 ,

If you want to add [Name] in Legend and display two lines based on Name, I suggest you to update code as below.

RollingMean6Months = VAR CurrentDate = MAX ( 'Charge'[Date] ) VAR SixMonthsAgo = EDATE ( CurrentDate, -6 ) RETURN AVERAGEX ( FILTER ( ALL( 'Charge'), Charge[Name] = MAX(Charge[Name]) && 'Charge'[Date] <= CurrentDate && 'Charge'[Date] > SixMonthsAgo ), 'Charge'[Value] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Line Chart Legend Rolling Average

Jihwan_Kim — Fri, 04 Oct 2024 03:43:10 GMT

Hi,

One of ways is to have dimension tables for date and company, and create one to many relationship to the fact table, that looks something like below.

Please check the below picture and the attached pbix file.

WINDOW function (DAX) - DAX | Microsoft Learn

sales 6 months rolling avg: = VAR _t = WINDOW ( -5, REL, 0, REL, ALL ( calendar_dim[Year-Month], calendar_dim[Year-Month sort] ), ORDERBY ( calendar_dim[Year-Month sort], ASC ) ) RETURN IF ( SUM ( sales_fact[value] ), AVERAGEX ( _t, CALCULATE ( SUM ( sales_fact[value] ) ) ) )

Re: Line Chart Legend Rolling Average

vjr37 — Fri, 04 Oct 2024 12:13:16 GMT

Thanks! This worked!

Re: Line Chart Legend Rolling Average

vjr37 — Fri, 04 Oct 2024 12:15:53 GMT

This also worked as well. I'd say this method follows appropriate standard practice too. Thanks for the help