Forum Discussion

adnanarain's avatar
adnanarain
Helper V
6 years ago
Solved

Need help to exclude data

Hi,

I have data until May 2019. In the above picture, the x-axis is date difference in months, the legend is year and I am using a measure for values. I want to show the following:

1. For the year 2019, the line chart should only show result till 0 to 5

2. For the year 2018, the line chart should show the result till 0 to 17 (12 months of 2018 and 5 months of 2019)

3. For the year 2017, the line chart should show the result till 0 to 29 (24 months of 2018 and 2017 and 5 months of 2019) and so on.

 

Thanks in advance for the help.

 

 

Need Help
  • CoalesceIsMore's avatar
    CoalesceIsMore
    6 years ago

    I'd suggest adding a column to your table such as this, then filter your chart to "Yes":

     

    DisplayOnlyValidPeriods = 
IF(Table1[Date Difference] <= (2019 - Table1[Account Open Year]) * 12 + 5,
    "Yes",
    "No"
)

     

    Presumably, this is a data set that will be occasionally updated. As such, you'll probably want to update the hard-coded 2019... logic to something that uses the current year/month minus whatever lag you want to include in the reporting.

  • adnanarain's avatar
    adnanarain
    6 years ago

    CoalesceIsMore Thank you so much for your answer. I have managed to get the desired result by using your calculated column but I have amended a little:

     

    DisplayOnlyValidPeriods = 
IF('Main Query'[z. Date Difference From Account Open to Month] <= (Year(Max('Main Query'[Month_Start_Date]))-1 - 'Main Query'[Account_Open_Date - Year Only]) * 12 + 'Main Query'[Max month],
    "Yes",
    "No"
)

     

    now I am planning to add a calculated table which will only store max month from my date. 

    Thanks for all the help.

11 Replies

  • PaulJ71's avatar
    PaulJ71
    Regular Visitor
    6 years ago

    If I was in this scenario I'd probably try and apply those rules to my data source rather than in the report. I've made assumptions that the data is stored in a database and the additional data isn't used somewhere else within your report.

    • adnanarain's avatar
      adnanarain
      Helper V
      6 years ago

      Thank you PaulJ71  for the reply. My data stored in excel file and i am using that data in other visuals so i can not exclude data from my data source. I only want to apply this condition in this visual only.

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

        Hi adnanarain ,

         

        Please show a sample data model.

        Best regards,
        Lionel Chen

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • CoalesceIsMore's avatar
      CoalesceIsMore
      Helper I
      6 years ago

      I'd suggest adding a column to your table such as this, then filter your chart to "Yes":

       

      DisplayOnlyValidPeriods = 
IF(Table1[Date Difference] <= (2019 - Table1[Account Open Year]) * 12 + 5,
    "Yes",
    "No"
)

       

      Presumably, this is a data set that will be occasionally updated. As such, you'll probably want to update the hard-coded 2019... logic to something that uses the current year/month minus whatever lag you want to include in the reporting.

      • adnanarain's avatar
        adnanarain
        Helper V
        6 years ago

        CoalesceIsMore  Thanks for the reply. for 2019 I can use the max function to get a current year but for 5, the data will change every month so basically when I will add June data then the 5 becomes 6 and I have to change that every month. If 5 can be dynamic then it will be great help