cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filter the [Date] column based on the user selecting an "End Date" only

Hi all. I want this to happen: 

1) User selects a specific [Date] in a slicer. Only one date. No ranges or anything like that. 

2) A corresponding table in the UI is filtered based on the selection. This table contains only the columns [Date] and an arbitrary aggregation - let's say sum('Facts'[Sales]). Both [Date] and [Sales] belong to the 'Facts' table in the data model btw.

3) However! I like would like the table to show not only the [Date] that was selected, but 30 days before that [Date] as well.

 

So far I have tried

TotalSales = 
CALCULATE(
	sum('Facts'[Sales]);
	FILTER(
		'Facts';
		'Facts'[Date] >= MAXX(ALL('Facts'[Date]);'Facts'[Date]) - 30   &&   'Facts'[Date] <= MAXX(ALL('Facts'[Date]);'Facts'[Date]) 
))

but that only works for he absolute latest [Date]. When a previous [Date] is selected, say a year ago, it yields no values. 
How woudl you go about solving this?

 

1 ACCEPTED SOLUTION

Hi @Anonymous,

Please follow the steps below.

1. Create a another new table named(New only DateTable) including continuous date,  then create two calculated columns using the formula below.

RANK = RANKX('New only DateTable','New only DateTable'[Date],,ASC)
30 day ago = LOOKUPVALUE('New only DateTable'[Date],'New only DateTable'[RANK],'New only DateTable'[RANK]-30)


2. Create a slicer including New only DateTable[Date]. Create a measure select the value clicked by you, another measure to get 30 days ago date selected.

selected value = MAX('New only DateTable'[Date])

30 day ago selected = CALCULATE(MAX('New only DateTable'[30 day ago]),ALLSELECTED('New only DateTable'))


3. Create a relationship between Date table(we called your date table as 'Date Dimension') and Sales Table based on [DateKey].

4. Create a measure to get the 30 days age's value.

 

30 dyas value = CALCULATE(MAX('Sales Table'[Sales]),FILTER('Date Dimension','Date Dimension'[Date]='New only DateTable'[30 day ago selected]))

 

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

If this does not resolve your issue, please create dummy data for better analysing.

 

Best Regards,
Angelia

View solution in original post

4 REPLIES 4
Solution Sage
Solution Sage

@Anonymous,

 

Can you try this,

 

Selected Date =
VAR ENDDATE = SELECTEDVALUE('Facts'[Date])
VAR STARTDATE = ENDDATE - 30
RETURN
CALCULATE(SUM('Facts'[Sales]),DATESBETWEEN('Facts'[Date],STARTDATE,ENDDate))

Anonymous
Not applicable

Good suggestion with the variables! I tried it and it does not work...
The user selects a date, and the table shows only that date now.

Hi @Anonymous,

Have you resolved your issue? If you have, welcome to share your solution or mark the helpful reply as answer, more people will get useful information here. Please respond to us if you have other issue.

Thanks,
Angelia

Hi @Anonymous,

Please follow the steps below.

1. Create a another new table named(New only DateTable) including continuous date,  then create two calculated columns using the formula below.

RANK = RANKX('New only DateTable','New only DateTable'[Date],,ASC)
30 day ago = LOOKUPVALUE('New only DateTable'[Date],'New only DateTable'[RANK],'New only DateTable'[RANK]-30)


2. Create a slicer including New only DateTable[Date]. Create a measure select the value clicked by you, another measure to get 30 days ago date selected.

selected value = MAX('New only DateTable'[Date])

30 day ago selected = CALCULATE(MAX('New only DateTable'[30 day ago]),ALLSELECTED('New only DateTable'))


3. Create a relationship between Date table(we called your date table as 'Date Dimension') and Sales Table based on [DateKey].

4. Create a measure to get the 30 days age's value.

 

30 dyas value = CALCULATE(MAX('Sales Table'[Sales]),FILTER('Date Dimension','Date Dimension'[Date]='New only DateTable'[30 day ago selected]))

 

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

If this does not resolve your issue, please create dummy data for better analysing.

 

Best Regards,
Angelia

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors