cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Samba777ravuri
Frequent Visitor

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

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.

Graph.PNG

3 REPLIES 3
edhans
Super User III
Super User III

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User III
Super User III

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors