cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Prakash4BI Regular Visitor
Regular Visitor

7 Day Moving Average Group By Latest selected date

 

Hi All,

 

Can you please provide the Dax formula to achieve the output from the below. Please find the below expression i am using as dax expression that is not working. In report used is production date and slicer as Record date.

 

CALCULATE([GasProduction (Sum)],ALLSELECTED(Well),DATESINPERIOD('Procount Record Date'[Date],LASTDATE('Procount Record Date'[Date]),-7,DAY))/7
 
Input:

 

image.png

Desired Result:

 

image.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: 7 Day Moving Average Group By Latest selected date

@Prakash4BI

 

Change 7 with 8:

7 day average = 
CALCULATE (
    SUM ( 'Procount Record Date'[Value] ),
    DATESINPERIOD (
        'Procount Record Date'[Date],
        LASTDATE ( 'Procount Record Date'[Date] ),
        -8,
        DAY
    )
)
    / 7

Capture.PNG 

Community Support Team _ Jimmy Tao

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

3 REPLIES 3
Community Support Team
Community Support Team

Re: 7 Day Moving Average Group By Latest selected date

@Prakash4BI,

 

Modify Measure like pattern below and check if it can meet your requirement:

Result =
CALCULATE (
    [GasProduction (Sum)],
    FILTER (
        'Procount Record Date',
        'Procount Record Date'[RelatedColumn] IN VALUES ( Well[RelatedColumn] )
    ),
    DATESINPERIOD (
        'Procount Record Date'[Date],
        LASTDATE ( 'Procount Record Date'[Date] ),
        -7,
        DAY
    )
)
    / 7

Community Support Team _ Jimmy Tao

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

Prakash4BI Regular Visitor
Regular Visitor

Re: 7 Day Moving Average Group By Latest selected date

I am modifying the DAX expression as below. Can you please share the desired DAX expression where the output show as in 2nd screen shot.

 

CALCULATE(
SUM(Procount[Net Flow Rate]),
DATESINPERIOD(Procount[Record Date],LASTDATE(Procount[Record Date]),-7,DAY))
/
7
Community Support Team
Community Support Team

Re: 7 Day Moving Average Group By Latest selected date

@Prakash4BI

 

Change 7 with 8:

7 day average = 
CALCULATE (
    SUM ( 'Procount Record Date'[Value] ),
    DATESINPERIOD (
        'Procount Record Date'[Date],
        LASTDATE ( 'Procount Record Date'[Date] ),
        -8,
        DAY
    )
)
    / 7

Capture.PNG 

Community Support Team _ Jimmy Tao

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