cancel
Showing results for
Did you mean:
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
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

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
2 REPLIES 2
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_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

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

!! Microsoft Fabric !!
Microsoft Power BI Learning Resources, 2023 !!
Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !!
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!