How to showcase the report based on a date selection before or after ?

[Anjaneyachari]

I have an effective date, an expression date, and a date picker to help me choose a date. If we choose a date from the date picker, the following condition should be met on the report.

Effective date <= Selected date and Expression date > Selected date

[Anjaneyachari]

@amitchandak  Thank you so much for responding.

This is my requirement.

I made Table_1 using the calendarauto function. This table was used as a slicer. I also create the relationship using the dim.date table. If we choose any date in the slicer. On Table_2, the conditions should be met. Full date, Effective Date, and Expiration Date should be displayed based on those conditions.

Conditions:

Full_Date <= selected date

AND  Effective_Date <= Selected date

AND  Expiration_Date > Selected date

Table_1

Selected Date

2022-01-31 00:00:00.000

2021-12-31 00:00:00.000

2022-03-31 00:00:00.000

2021-09-14 00:00:00.000

2022-02-28 00:00:00.000

  

Table_2
Col_1	Col-2	Col_3	Sale	Full_Date	Effective_Date	Expiration_Date
DC	TC	PREMOP	0	2022-01-31 00:00:00.000	2021-08-22 00:00:00.000	2022-08-22 00:00:00.000
DC	TC	PREMOP	143	2021-12-31 00:00:00.000	2021-12-31 00:00:00.000	2022-12-31 00:00:00.000
DC	TC	PREMOP	0	2022-03-31 00:00:00.000	2021-10-01 00:00:00.000	2022-10-01 00:00:00.000
DC	TC	PREMOP	3354	2021-09-14 00:00:00.000	2021-09-14 00:00:00.000	2022-09-14 00:00:00.000
DC	GL	PREMOP	0	2022-02-28 00:00:00.000	2021-09-18 00:00:00.000	2022-09-18 00:00:00.000

Please assist me. Great thanks......!

[amitchandak · Accepted solution]

@Anjaneyachari , Prefer an independent table for slicer

before

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] <_max))

after

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] > _max))

if do not create independent table data will rollup one date

example

before =
var _max = maxx(allselected(Date),Date[Date])
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] <_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI