Forum Discussion

NehaSha's avatar
NehaSha
Icon for Helper II rankHelper II
7 years ago
Solved

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

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 :

 NetRentGPI NetRentLYGPILY
1/1/2019100015001/1/2018500700
1/2/2019100115011/2/2018501701
1/3/2019100215021/3/2018502702
1/4/2019100315031/4/2018503703
1/5/2019100415041/5/2018504704
1/6/2019100515051/6/2018505705
1/7/2019100615061/7/2018506706
1/8/2019100715071/8/2018507707
1/9/2019100815081/9/2018508708
1/10/2019100915091/10/2018509709
1/11/2019101015101/11/2018510710
1/12/2019101115111/12/2018511711
1/13/2019101215121/13/2018512712
1/14/2019101315131/14/2018513713
1/15/2019101415141/15/2018514714
1/16/2019101515151/16/2018515715
1/17/2019101615161/17/2018516716
1/18/2019101715171/18/2018517717
1/19/2019101815181/19/2018518718

 

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

Need Help
  • ElenaN's avatar
    ElenaN
    7 years ago

    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

2 Replies

  • ElenaN's avatar
    ElenaN
    Icon for Resolver V rankResolver V
    7 years ago

    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's avatar
      NehaSha
      Icon for Helper II rankHelper II
      7 years ago

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