cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MP-iCONN
Helper I
Helper I

Custom date filter for last 8 weeks including today plus 365 days from today

I am trying to create a filter for a required date field in my table.  We want to see all sales based on the required date.  This filter will include the last 8 weeks, today, and 365 days from today.

 

I have a Calendar table setup in my model already but unsure how to relate them together to my 'Sales Orders'[Required Date] column.

 

Thank you for any help on this!

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @MP-iCONN ,

 

Create a table that contains 3 rows: last 8 weeks, today, and 365 days from today.

Use this table as slicer.

Create 3 measure:

measure1 = return 1 if the dates in sales table are in last 8 weeks, else rerutn 0.

measure2 = return 1 if the dates in sales table is today, else rerutn 0.

measure3 = return 1 if the dates in sales table are in last 365 days, else rerutn 0.

Create an additional measure:

Swith(selectedvalue([slicer]),"last 8 weeks",measure1,"today",[measure2],"365 days from today",[measure3],0)

Then add this measure to visual filter which value = 1.

 

Best Regards,

Jay

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

1 REPLY 1
v-jayw-msft
Community Support
Community Support

Hi @MP-iCONN ,

 

Create a table that contains 3 rows: last 8 weeks, today, and 365 days from today.

Use this table as slicer.

Create 3 measure:

measure1 = return 1 if the dates in sales table are in last 8 weeks, else rerutn 0.

measure2 = return 1 if the dates in sales table is today, else rerutn 0.

measure3 = return 1 if the dates in sales table are in last 365 days, else rerutn 0.

Create an additional measure:

Swith(selectedvalue([slicer]),"last 8 weeks",measure1,"today",[measure2],"365 days from today",[measure3],0)

Then add this measure to visual filter which value = 1.

 

Best Regards,

Jay

 

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.