Fetch the 7 days Average of sales should be calculated dynamically in graph level

[Samba777ravuri]

Hi Folks,

I have data for 30 days but we want to show last 7 days of sum of sales in Clustered column chart in Date level.

Date should be selcted by user dynamically.

Existing Measure:

Avg Sales = VAR selected_date =

SELECTEDVALUE ( dimDate[DayDate] )

RETURN  CALCULATE (

DIVIDE(

SUM('factWareWashEvents'[LowRinseTempSum]),

SUM('factWareWashEvents'[RackCount])

),

FILTER ( Date_Table,  Date_Table[DayDate] > selected_date - 7 && Date_Table[DayDate] <= selected_date

)

)

but here the output is calculating by day level of avg if user is selected the 3rd jan in Slicer. My motto is need to get last 7 days of avg dynamically. i.e till 3rd jan to 28th Dec of sum of measure and divided by 7 is my output.

Same happens for 2nd june to 27 dec and divided by 7 is the output of 2nd jun in Axis level.

Please help me on this issue.

[edhans]

and the user is selecting one date? SELECTEDVALUE() returns blank if htere are 2+ dates selected.
And the Date table is marked as a date table, and is filtering your Factswashwearevents table?

It would be helpful to have some data.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[edhans]

Try this. 

Avg Sales =
VAR selected_date =
    SELECTEDVALUE( dimDate[DayDate] )
RETURN
    CALCULATE(
        DIVIDE(
            SUM( 'factWareWashEvents'[LowRinseTempSum] ),
            SUM( 'factWareWashEvents'[RackCount] )
        ),
        FILTER(
            ALL( Date_Table[DayDate] ),
            Date_Table[DayDate] > selected_date - 7
                && Date_Table[DayDate] <= selected_date
        )
    )

You have to remove the filter from the Date column.

[Samba777ravuri]

now the avg is showing same for all the dates in graph