Rolling 7 Dates based on selected date in dropdown

[Danish]

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

[Phil_Seamark]

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)

[Danish]

Thanks for reply,

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

[v-huizhn-msft · Accepted solution]

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

[Ant21]

Hi @v-huizhn-msft

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?

Thank you for your time. 

[anil]

Hi

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

[Ant21]

Hi @v-huizhn-msft

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?

[Danish]

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