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

CoalesceIsMore's avatar
CoalesceIsMore
Helper I
6 years ago

To your first question (when you add June data then the 5 becomes 6), I don't think you'll be able to do that unless you either add the actual month number to your dataset, or if you can make an assumption about the latency of the data (e.g. it's always 14 months before today).

 

On your second question (sort order)... When you select the chart, you should see a ... at the right side of the chart (could be either upper or lower). Select that to ensure your Sort By is being done on your date diff. You may also need to confirm those are formatted as numbers so they don't have leading spaces that could be affecting it.

adnanarain's avatar
adnanarain
Helper V
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.