Date - Last 7 / 30 days - recurring

[svellik]

I use Power BI desktop. I want to make a report that show users incoming application, and I want it to show number of application last 7 days and last 30 days. And I want this to be recurring date, so the users don`t have to use filters.

What is the recommended way to do this in my data set up and reports?

Thanks in advance for any answers.

[vanessa]

Is there a way to do this, if the 7 days are not calculated from today(), but based on a date selected from a dropdown list.

eg. suppose the date selected in the dropdown is 18/07/2017.

so 7 days before 18/07/2017 should be in the result.

[LetBloodline]

I wanted to do something similar with last12month

My solution was to create a calculated column on calendar table

Last12m = IF(TODAY()<'Date'[InizioMese];
 1+QUOTIENT(
  DATEDIFF(TODAY();
   'Date'[InizioMese];
   MONTH)-1;
  12);
 0-QUOTIENT(
  DATEDIFF('Date'[InizioMese];
   TODAY();
   MONTH);
  12)
 )

i can then use Last12m the same way i'd use Year for filtering or anything else

Note that i needed to include full current month in last12m (today last12m = 0 it would be from 1/nov/14 to 31/oct/15. If you want last 30 days just work with days

[itchyeyeballs]

Need to be careful if using a calculated column as I don't think it will be refreshed automatically without a data source refresh. The calculation could be correct when first set up but then fall over the next day.

[mllopis · Accepted solution]

Hi,

You can set "dynamic filters" as part of your query. It is available to you in the Filter menu (column header dropdown menu) for a DateTime column.

Depending on your queries, you might want to have a common base query and then create two "reference" queries where you define a different filter on each. You could then also disable load for the common base query, and simply load the other two tables with "Last 7 days" and "Last 30 days" data.

Thanks,
M.

[jariwalakrunal]

Hi, I didn't find how to reach filter menu? Can you pls explain?

[ALeef]

@jariwalakrunal : If you open up "Edit Queries" on the ribbon under "Home" tab, click on the Query you want to modify on the left, Query1 for example - and then click on a column header that contains Date/Time Data.  Then about 5 options down you will see "Date/Time Filters" and an arrow pointing right.  Use those to set your filter.  

Beware that if you want to have past 7 and past 30, you will need to duplicate the query, and change that filter.

[ElliotP]

@ALeef

Good idea! In doing this; would it best to have a seperate table just with the date and the data we want and use the filters? Will it update everytime we refresh the dashbard?

Do you think there's a better way to do this as so you don't have to create a new table everytime?

Something like probs a calculated column:

Past 7 Day Sales = Calculate(Values('Sales Data'),     
         DATESINPERIOD (
        'Datetable'[DateKey],
        LASTDATE ( 'Datetable'[DateKey] ),
        -7,
        DAY
    )

The part I'm not sure about is the VALUES part. I feel we need a different expression to literally just show the last few days actual values.

[jariwalakrunal]

Hi,

This is helpful, thank you. 🙂

However, I didn't get how to set it to last 30 days or last 7 days for example!

Krunal