Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

7 Day Moving Average not working correctly

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:

image.png

Desired Result:

image.png

 

 

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

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

 

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

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,

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

View solution in original post

6 REPLIES 6
v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

Could you please mark the proper answers as solutions?

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft
Employee
Employee

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft
Employee
Employee

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@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:

 

MeasureDate
449.389078512/31/2018 0:00
449.37815712/30/2018 0:00
461.470989812/29/2018 0:00
463.460068312/28/2018 0:00
473.48737212/27/2018 0:00
466.394539212/26/2018 0:00
482.46552912/25/2018 0:00
 Required Result 
  
MeasureDate
463.720819112/31/2018 0:00

 

 

 

Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.