Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Select one date to filter data between two dates

Hi team, wondering if I can please get some help with this.

I have an asset table with DateFrom and DateTo. A new row is created in the database on the following date when there has been a change to the asset. See example below. I also have a separate Date table.



I want to be able to select one particular date on a slicer, and see what the attributes of my asset were at that date only.
This date may fall on the start or end dates, or within those dates. 


For example, if I selected 1/6/2019, my asset amount would be 60.

Or if I selected 1/12/2019 my asset amount would be 150.

 

Seems pretty straight forward but I'm unsure. Help much appreciated.

Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Anonymous ,

     

    You could try the following measure:

    Measure =
VAR a =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE (
        CALCULATE ( MAX ( 'Table'[Amount] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
        FILTER ( 'Table', 'Table'[DateFrom] <= a && 'Table'[DateTo] >= a )
    )

     

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , Try with disconnected date table. That Dat table should be used in slicer

     

    measure =
    var _min = minx(allselected(Date),Date[Date])
    return calculate(sum(Table[Amount]), filter(Allselected(Table),Table[DateFrom]<=_min && Table[DateTo]>=_min))

     

    In the case, the date table is connected use crossfilter to remove join

    example in this blog. Also if this blog's current employee calc can help you.

     

    https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

     

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    Anonymous 

    Try this measure, and add it in the visual filter, hope you haven't connected the date table to the asset table, it should disconnected dates table

     

    m_DateSelected = 
VAR _DATE = SELECTEDVALUE(Dates[Date])

RETURN

IF(
    _DATE >= MAX(ASSETS[DATEFROM]) && _DATE <= MAX(ASSETS[DATETO]),
    1,
    0
)

     

    If you are satisfied with my answer, please mark it as a solution so others can easily find it.

    Don't forget to give KUDOS ? to replies that help answer your questions


    Subscribe to ExcelFort: Learn Power BI, Power Query and Excel

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks; this does work but as soon as I summarize the data the totals seem to blow out. 

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    You could try the following measure:

    Measure =
VAR a =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE (
        CALCULATE ( MAX ( 'Table'[Amount] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
        FILTER ( 'Table', 'Table'[DateFrom] <= a && 'Table'[DateTo] >= a )
    )