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

[Anonymous]

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?

[SivaMani]

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

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.

[v-huizhn-msft]

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

[v-huizhn-msft · 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