Need Help Creating a Stacked Area Chart for the 30 Days Before a User Selected Date

[tbruech]

Hello Everyone,

I've been trying to create a stacked area chart that shows sales data for three different sub product types for the last 30 days from a user selected date. The data comes from a table which contains daily sales and where each row represents sales for a single product for a single day, with the sales of sub-products for each day included as well.

A user selects a date to display data by using a page level filter with Advanced filtering and "is" for single selection. Most of the report shows KPIs for the individual day that is selected.

I previously tried adding a custom table that filters so that it only contains data for the user selected date and 30 days before. However, since custom tables are evaluated during data refresh, this was not dynamic for when a user changes the date. The behavior for this was that the chart would always show the 30 days previous to the most recent date in the data.

Here is an example of what my chart looks like:

Is there a way to display the 30 days prior to a selected date in a chart, while also showing the KPIs for a single day in the rest of the report?

Let me know if you have any clarifying questions.

Thanks,

Trevor

[v-juanli-msft]

Hi @tbruech 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[amitchandak]

Try

Rolling 30 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],maxx(sales,Sales[Sales Date]),-30,DAY))   
Rolling last 30 before 30 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],(dateadd('Date'[Date],-30,DAY)),-30,DAY))  

Day 30 behing= CALCULATE(sum(Sales[Sales Amount]),dateadd('Date'[Date]30,DAY))  

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[tbruech]

@amitchandak

Thank you for the advice, I incorporated a date table into my report, however, to clarify my question, each data point in my chart represents the data for a single day and not the 30 day rolling sum. 

I want my report to filter on a single date that a user chooses and all the KPIs in the page will then display for this single day. The data in the chart should show the selected date as well as the 29 other days prior.