Forum Discussion

svellik's avatar
svellik
Frequent Visitor
10 years ago
Solved

Date - Last 7 / 30 days - recurring

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, s...
Need Help
  • mllopis's avatar
    mllopis
    10 years ago

    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's avatar
jariwalakrunal
New Member
10 years ago

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

ALeef's avatar
ALeef
Icon for Resolver II rankResolver II
10 years ago

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.

  • jariwalakrunal's avatar
    jariwalakrunal
    New Member
    10 years ago

    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

  • ElliotP's avatar
    ElliotP
    Icon for Post Prodigy rankPost Prodigy
    10 years ago

    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.