Single Date Selection filtering data up to the selected date

[Anonymous]

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.

Example:

If the users selects 01/15/2019, then data should be filtered from 01/01/2019 to 01/15/2019.

If the users selects 12/26/2018, then data should be filtered from 12/01/2018 to 12/26/2018.

This is a kind of dynamic MTD.

Need help in achieving this requirement

[v-piga-msft]

Hi @Anonymous ,

It seems that you want to create the MTD.

If you have had a calendar table you could create the MTD measure with the formula below. If you don't have the calendar table , you could create it with CALENDARAUTO() function then create the relationship between the tables with Date column.

MTD = TOTALMTD(SUM('Table'[Amount]),'Calendar'[Date])

Here is the output.

More details, please refer to my attachment.

In addition, you could refer to this blog whihc may help you.

Calculating MTD, QTD, YTD, Running and Cumulative Total in Power BI

If you still need help, please share your data sample so that we could help further on it.

Best  Regards,

Cherry

[Anonymous]

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 · Accepted solution]

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]

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