cancel
Showing results for
Did you mean:
Frequent Visitor

## Rolling 7 Dates based on selected date in dropdown

Hi,

I want to calculate the Rolling 7 Dates based on selected date in dropdown. The dropdown selection coming from Date Dimension. I found solutions for calculating the Rolling Dates based on TODAY(), but i want to get the Rolling Dates based on selected date.

Thanks,

Danish

1 ACCEPTED SOLUTION
Microsoft

Hi @Danish,

I try to reproduce and get expected result, In following steps,

First, please click "New Table" under Modeling on home page, type the following formula and create a new table.

`New only DateTable = SELECTCOLUMNS('Sales Table',"Date",'Sales Table'[Date])`

2. Create calculated column to get 7 days ago date.

```RANK = RANKX('New only DateTable','New only DateTable'[Date],,ASC)

7 day ago = LOOKUPVALUE('New only DateTable'[Date],'New only DateTable'[RANK],'New only DateTable'[RANK]-7)```

3. Create a slicer including New only DateTable[Date].

Create a measure select the value clicked by you, another measure to get 7 days ago date selected.

`selected value = MAX('New only DateTable'[Date])7 day ago selected = CALCULATE(MAX('New only DateTable'[7 day ago]),ALLSELECTED('New only DateTable'))`

4.  Create a relationship between Date Dimension and Sales Table based on [DateKey].

5. Create a measure.

`7 dyas value = CALCULATE(MAX('Sales Table'[Sales]),FILTER('Date Dimension',AND('Date Dimension'[Date]<=[selected value],'Date Dimension'[Date]>'New only DateTable'[7 day ago selected])))`

Create a table, select 'Date Dimension'[Date], 'Sales Table'[Sales] and the [7 dyas value] as value level. You will get expected result when you select different values in slicer.

Best Regards,
Angelia

7 REPLIES 7
Microsoft

This should still work.

Can you please post a small sample of your data so we can help build the measure for you.

Just to be sure, are you after a single number that shows the rolling average for the 7 days, or do you want to display 7 date points based on your date selection? (Both are achieveable)

Proud to be a Datanaut!

Frequent Visitor

Following may be Date Dimension Data,

 DateKey Date 20170212 2/12/2017 20170213 2/13/2017 20170214 2/14/2017 20170215 2/15/2017 20170216 2/16/2017 20170217 2/17/2017 20170218 2/18/2017 20170219 2/19/2017 20170220 2/20/2017 20170221 2/21/2017 20170222 2/22/2017 20170223 2/23/2017 20170224 2/24/2017 20170225 2/25/2017

and following is the Sales data.

 DateKey Sales 20170212 5 20170213 10 20170214 25 20170215 35 20170216 22 20170217 15 20170218 30 20170219 10 20170220 20 20170221 15 20170222 20 20170223 30 20170224 40 20170225 25

Now i want to show the bar graph showing sales date wise. I have a DropDown selector on Date column. If I select 2/24/2017 then graph should show the dates and sales of 2/18/2017, 2/19/2017, 2/20/2017, 2/21/2017, 2/22/2017, 2/23/2017, 2/24/2017. And if i change the date in the selector to 2/22/2017, then graph should show the dates as 2/16/2017, 2/17/2017, 2/18/2017, 2/19/2017, 2/20/2017, 2/21/2017, 2/22/2017. and so on.

Thanks,

Danish

Microsoft

Hi @Danish,

I try to reproduce and get expected result, In following steps,

First, please click "New Table" under Modeling on home page, type the following formula and create a new table.

`New only DateTable = SELECTCOLUMNS('Sales Table',"Date",'Sales Table'[Date])`

2. Create calculated column to get 7 days ago date.

```RANK = RANKX('New only DateTable','New only DateTable'[Date],,ASC)

7 day ago = LOOKUPVALUE('New only DateTable'[Date],'New only DateTable'[RANK],'New only DateTable'[RANK]-7)```

3. Create a slicer including New only DateTable[Date].

Create a measure select the value clicked by you, another measure to get 7 days ago date selected.

`selected value = MAX('New only DateTable'[Date])7 day ago selected = CALCULATE(MAX('New only DateTable'[7 day ago]),ALLSELECTED('New only DateTable'))`

4.  Create a relationship between Date Dimension and Sales Table based on [DateKey].

5. Create a measure.

`7 dyas value = CALCULATE(MAX('Sales Table'[Sales]),FILTER('Date Dimension',AND('Date Dimension'[Date]<=[selected value],'Date Dimension'[Date]>'New only DateTable'[7 day ago selected])))`

Create a table, select 'Date Dimension'[Date], 'Sales Table'[Sales] and the [7 dyas value] as value level. You will get expected result when you select different values in slicer.

Best Regards,
Angelia

New Member

Great work ! thank you!

I have run into a little problem when trying to impliment this and I was wondering if you could be so kind and help me!?

I have followed your steps and everything works create when using a slicer with the "date" coming from the New only DateTable.

When though trying to link this slicer with our main slicer {Date coming from the DateDimension table] for our report as I have a lot more graphs/tables to filter at the same time [by date] ...the 7 days ago data disappear and I only get data for the date selected.

I know the problem occurs when i create a relationship between the DateDimension table  and the New only DateTable. Is there a way to by pass this problem?

Helper III

Hi

Have you found any final solution for the problem. I am facing similar issue in my implementation

New Member

Great Work!

I am having some difficulties with the slicer. If I use a slice with the "date" from the new only DatesTables this works like a charm but once I link that to the Date Dimension table as i have lots of other stuff on my report and I want the date selection to be centrally controlled. Once i do that though the 7 days before disappear and only the date selected appears. I know there is something wrong with the relationship created but I am uncertain how to resolve it.

Could you be so kind and guide me through this?

Frequent Visitor

Thanks Angelia 🙂

Nice and tricky logic for me. Its worked for the sample data, I will implement it in my actual requirement and hopefully it would work in there too.

Thanks,

Danish