Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Danish
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

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])


1.PNG

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)


7.PNG

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

2.PNG


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].

3.png

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.

6.PNG8.PNG

Best Regards,
Angelia






View solution in original post

7 REPLIES 7
Phil_Seamark
Microsoft Employee
Microsoft Employee

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)

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks for reply,

 

Following may be Date Dimension Data,

 

DateKeyDate
201702122/12/2017
201702132/13/2017
201702142/14/2017
201702152/15/2017
201702162/16/2017
201702172/17/2017
201702182/18/2017
201702192/19/2017
201702202/20/2017
201702212/21/2017
201702222/22/2017
201702232/23/2017
201702242/24/2017
201702252/25/2017

 

and following is the Sales data.

 

DateKeySales
201702125
2017021310
2017021425
2017021535
2017021622
2017021715
2017021830
2017021910
2017022020
2017022115
2017022220
2017022330
2017022440
2017022525

 

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

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])


1.PNG

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)


7.PNG

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

2.PNG


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].

3.png

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.

6.PNG8.PNG

Best Regards,
Angelia






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. 

Hi

 

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

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?

 

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.