How to return values based on if dates are within Slicer date range

[NehaSha]

Hi All,

I am new to Power BI& and need help to resolve the below mentioned scenario

Report Need to show

Date Range Slicer& Card 1: value for SUM(Net Rent) based on Date Range Card 2:value for GP based on end date in slicer

Card 4 : value for GP L Y

For Example Date Range slicer set by user to 1/01/2019 to 1/19/2019

but min value: 01/01/2013 to max value: 2/19/2019

Mock up Table :

	NetRent	GPI		NetRentLY	GPILY
1/1/2019	1000	1500	1/1/2018	500	700
1/2/2019	1001	1501	1/2/2018	501	701
1/3/2019	1002	1502	1/3/2018	502	702
1/4/2019	1003	1503	1/4/2018	503	703
1/5/2019	1004	1504	1/5/2018	504	704
1/6/2019	1005	1505	1/6/2018	505	705
1/7/2019	1006	1506	1/7/2018	506	706
1/8/2019	1007	1507	1/8/2018	507	707
1/9/2019	1008	1508	1/9/2018	508	708
1/10/2019	1009	1509	1/10/2018	509	709
1/11/2019	1010	1510	1/11/2018	510	710
1/12/2019	1011	1511	1/12/2018	511	711
1/13/2019	1012	1512	1/13/2018	512	712
1/14/2019	1013	1513	1/14/2018	513	713
1/15/2019	1014	1514	1/15/2018	514	714
1/16/2019	1015	1515	1/16/2018	515	715
1/17/2019	1016	1516	1/17/2018	516	716
1/18/2019	1017	1517	1/18/2018	517	717
1/19/2019	1018	1518	1/19/2018	518	718

Card1: SUM(NetRent) where date between 1/01/2019 to 1/19/2019 --Done --1971

Card2: GPI value where date=1/19/2019 --need help to get the latest value based on end date--1518

Card3: SUM(NetRent) where date between 1/01/2018 to 1/19/2018 --Done with sameperiod LY --9671

Card4:GPI LY value where Date=1/19/2018 --need help to get the latest value based on end date --718

1. How to pass filter for GPI to get the latest value without any aggregation based on end date pass by end user 

2. How to get the GPI LY based on above mention filter

Thanks,

Neha

[ElenaN · Accepted solution]

Hello,

You can try this:

GPIEndDate = 
VAR LastSelectedDate = LASTDATE(ALLSELECTED(Table3[Date]))
RETURN
CALCULATE(SUM(Table3[GPI]), FILTER(Table3, Table3[Date] = LastSelectedDate))

GPILYEndDate = 
VAR LastSelectedDate = LASTDATE(ALLSELECTED(Table3[Date])) 
RETURN CALCULATE(SUM(Table3[GPILY]), FILTER(Table3, Table3[Date] = LastSelectedDate))

Regards,

ElenaN

[NehaSha]

Working fine and got the expected results! Thank you very much!