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

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

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'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