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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anjaneyachari
Frequent Visitor

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

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

 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

View solution in original post

2 REPLIES 2
Anjaneyachari
Frequent Visitor

@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_1Col-2Col_3SaleFull_DateEffective_DateExpiration_Date
DCTCPREMOP02022-01-31 00:00:00.0002021-08-22 00:00:00.0002022-08-22 00:00:00.000
DCTCPREMOP1432021-12-31 00:00:00.0002021-12-31 00:00:00.0002022-12-31 00:00:00.000
DCTCPREMOP02022-03-31 00:00:00.0002021-10-01 00:00:00.0002022-10-01 00:00:00.000
DCTCPREMOP33542021-09-14 00:00:00.0002021-09-14 00:00:00.0002022-09-14 00:00:00.000
DCGLPREMOP02022-02-28 00:00:00.0002021-09-18 00:00:00.0002022-09-18 00:00:00.000


Please assist me. Great thanks......!
 
amitchandak
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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