Rolling 7 average - graph not showing last 7 days

[sh001]

Hi,

I'm trying to create a rolling 7 day average. I've created a calendar table, linked that to one of my tables (I'm using data from 3 tables in the chart). The calculation I'm using is:

Rolling AVG - 7 days =

VAR temp =

CALCULATE (

SUMX (

SUMMARIZE (

'CALENDAR',

'CALENDAR'[Date].[Date],

"sumtotal", [Issues]

),

[sumtotal]

),

DATESINPERIOD (

'CALENDAR'[Date],

Max ( 'CALENDAR'[Date] ), -7, DAY)

)

RETURN temp/7

Where "Issues" is a calculated measure. The actual calculation seems to work fine, however, in a chart the last day that is being displayed is 7 days ago. I need the chart to show a rolling 7 day period upto and including today.

Regards

Steve

[v-kelly-msft]

Hi  @sh001 ,

Your dax formular seems ok, could you pls first test whether below expression returns correct values?

SUMX (

SUMMARIZE (

'CALENDAR',

'CALENDAR'[Date].[Date],

"sumtotal", [Issues]

),

[sumtotal]

),

As tested here,when I remove  the first part of the formular,it works fine,see below:

Or can you upload your .pbix file to Onedrive business and share the link with us?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[lbendlin]

Your Max ( 'CALENDAR'[Date] )  computation needs to happen outside the CALCULATE() to escape the filter context.

var md = calculate(Max ( 'CALENDAR'[Date] ), <desired scope>)

desired scope could be ALL(Calendar)  or ALLSELECTED()  etc.

Then use that variable in your DATESBETWEEN statement.

[sh001]

Hi @lbendlin 

Many thanks for you reply, what have I done wrong, the below is not calculating anything now. Sorry, I'm new to Power Bi.

Rolling AVG - 7 days =

VAR md=CALCULATE(max('CALENDAR'[Date]),all('CALENDAR'))

VAR temp =

CALCULATE (

SUMX (

SUMMARIZE (

'CALENDAR',

'CALENDAR'[Date],

"sumtotal", [Issues]

),

[sumtotal]

),

DATESINPERIOD('CALENDAR'[Date],md,-7,day)

)

RETURN temp/7

Cheers

Steve

[v-kelly-msft]

Hi  @sh001 ,

No ,you cant create a variable as below:

VAR md=CALCULATE(max('CALENDAR'[Date]),all('CALENDAR'))

It means you are finding out the maximun date in the calendar table.

Could you pls upload your .pbix file to Onedrive business and share the link with me?Or could you provide some sample data for me to check the calculation?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!