Forum Discussion
Bring in rolling 30 days data
Hello community,
I have a table in Snowflake that has about 85mil records. I want to only bring in rolling 30 days data.
How do I go about doing that in Power bi?
My columns are Transaction_date(mm/dd/yyyy), sales, qty etc
Thank you.
Anonymous
As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel
Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence PlaylistAdd a following measure for rolling 30 days:
Rolling 30 Days = VAR __Days = -30 RETURN CALCULATE ( SUM ( Table[Sales] ), DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), __Days, DAY ) )
4 Replies
Anonymous that is part of Power Query that you can do to filter the data for x number of days. The solution I gave is to visualize rolling 30 days of data.
The preferred method is to create a view at snowflake and let it give you 30 days of data and then use that view in Power BI, keeping that logic close to the backend system.
Anonymous
As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel
Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence PlaylistAdd a following measure for rolling 30 days:
Rolling 30 Days = VAR __Days = -30 RETURN CALCULATE ( SUM ( Table[Sales] ), DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), __Days, DAY ) )This looks great, thanks for all the info.
Is it possible to only include rolling 30 days instead of bringing all 2-3 year worth of data from Snowflake?