7 Day Moving Average not working correctly

[Anonymous]

Hi All,

I am creating a report using SSAS Tabular live connection. Client wants to report all the measures should be created in cube itself. I tried 7 Day Avg below measure in cube, it is not giving the exact result what i am looking. In my report i am using two dates one is record date is as date slicer and production date is in report. I want to show only one aggregated value for the 7 day avg that should be aggregate of past 7 days record date moving average and latest production date as showin below screen shot. In my case, the measure is splitting into 7 parts when i inlude production date in report. I want to show only one record for one day record date slicer not 7 days splitted below. 

Expression i used:

7-Day Avg = 
Var Intervel = -7
RETURN
CALCULATE(
SUM(Procount[Net Flow Rate]),
DATESINPERIOD(Procount[Record Date],LASTDATE(Procount[Record Date]),Intervel,DAY))
/
7

Result i am getting when i add produciton date in report:

Desired Result:

Gas production and net flow rate both are same base measures.

7-Day avg & Rolling 7 day sum both are same.

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answers as solutions?

Best Regards,

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

It looks like a context issue. If you put the [GasProduction(sum)] and the [Rolling 7 day SUM] along with the [Production Date], I think you will get the right result. 

I would still suggest you create a date table. The dates in the visual will be from this table.

Calendar = calendar(date(2010, 1,1), date(2019, 12, 31))

Best Regards,

[v-jiascu-msft]

Hi @Anonymous,

Firstly, the datesinperiod-function-dax is a time intelligence function that needs a continuous date table. 

Secondly, the dates in the formula are [Record Date] while it's [Date] in the visual. That could cause an issue. 

Can you share a sample file, please? Please mask the sensitive parts first.

Best Regards,

[Anonymous]

@v-jiascu-msft

Here is my sample data. Where i am getting proper ouput from import method and Live connection i am calling this measure from cube. Due to this it is not giving desired output.

Input:

Measure	Date
449.3890785	12/31/2018 0:00
449.378157	12/30/2018 0:00
461.4709898	12/29/2018 0:00
463.4600683	12/28/2018 0:00
473.487372	12/27/2018 0:00
466.3945392	12/26/2018 0:00
482.465529	12/25/2018 0:00
Required Result
Measure	Date
463.7208191	12/31/2018 0:00

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Anonymous]

Hi Greg, Thanks for your reply. My scenario is different, problem still exists same.