Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Single Date Selection filtering data up to the selected date

I have a requirement where I need a single date filter selection like below.   When the user selects any date, the report should filter data from the first of that month to selected date.   ...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi Anonymous ,

     

    If your table contains the continuous date column, you could try the way below. If your date column is not continuous, I'm afraid that you should create a table with the date missed and then join the tables to get a continuous date column.

     

    1. Create the Calendar table and don't create the relationship between the original table and the Calendar table.

     

    2. Create the Date slicer with the date column in Calendar table.

     

    3. Create a measure with the formula below to get the records base on your selection.

    Measure = IF(SELECTEDVALUE('Table'[Date])<=SELECTEDVALUE('Calendar'[Date]),1,0)

    4. Create the MTD measure with the formula.

     

    MTD =
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        MONTH ( 'Table'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) )
            && 'Table'[Date] <= MAX ( 'Table'[Date] )
    )
)

    Here is the output.

    More details, please refer to the attachment.

     

    Best Regards,

    Cherry

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi v-piga-msft ,

 

In addition to the MTD, I also need to filter the records in the table.

In your attached screenshot, there is a table with Date, Amount and MTD column. Also there is a Date filter. 

When we select any date from the date filter, the table should also be filtered.

 

If we select 1/3/2017 from the date filter, then data for 1/1/2017, 1/2/2017 and 1/3/2017 should be displayed in the table. Therefore only 3 records should be displayed in the table.

 

If we select 1/10/2017, then data from 1/1/2017 to 1/10/2017 should be displayed.

 

Thank you for your help.

 

Regards,

Ravin

v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
7 years ago

Hi Anonymous ,

 

If your table contains the continuous date column, you could try the way below. If your date column is not continuous, I'm afraid that you should create a table with the date missed and then join the tables to get a continuous date column.

 

1. Create the Calendar table and don't create the relationship between the original table and the Calendar table.

 

2. Create the Date slicer with the date column in Calendar table.

 

3. Create a measure with the formula below to get the records base on your selection.

Measure = IF(SELECTEDVALUE('Table'[Date])<=SELECTEDVALUE('Calendar'[Date]),1,0)

4. Create the MTD measure with the formula.

 

MTD =
CALCULATE (
    SUM ( 'Table'[Amount] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        MONTH ( 'Table'[Date] ) = MONTH ( MAX ( 'Table'[Date] ) )
            && 'Table'[Date] <= MAX ( 'Table'[Date] )
    )
)

Here is the output.

More details, please refer to the attachment.

 

Best Regards,

Cherry

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi v-piga-msft ,

     

    Thank you for your help.

     

    I needed that, If we select 1/10/2017, then data from 1/1/2017 to 1/10/2017 should be displayed.

    If we select 2/15/2018, then data from 2/1/2018 to 2/15/2018 should be displayed, rest all should be filtered.

     

    Your solution was very much helpful.

    I just modified your measure formulae and it worked for me.

     

    Regards,

    Ravin