Hi,
I am trying to filter out blank in my report where the past date in the date table, e.g 1990, 1991 have no data and the future date e.g, 2021, 2022 have no data.
The report works fine except with blanks in the past and future date. How do I resolve this, please?
Thank you
there are many way to exclude blanks.
You can use Filter pane or Measure to avoid null values
Is it possible to share sample PBIX file ?
Hello @upwardD,
You may solve the problem with a calculated column inside your date table.
I recommend reading this blogpost:
1 Like
Hello @upwardD,
Thank You for posting your query onto the Forum.
Well the first thing is if you don’t have the data for the previous years than it’s point to keep the dates for those periods it will just unnecessarily bloat the size of the model. You can remove those periods from your date table. Below is the screenshot provided for the reference -
And for the future dates you can restrict the values by using the measures so that the last values are not repeated. Whilst in order to prevent the future dates you can also use the option of “IsAfterToday” and put it under the FIlter Pane section and un-check the option of “True”. As shown in the screenshot provided below -
I’m providing the link below from where you can copy the M Code of the Date table and paste it in your Advanced Editor and then use the given option as suggested above. Also I’m attaching the working of the PBIX file for the reference showing how you can restrict the values for future dates.
Hoping you find this useful. 
Thanks and Warm Regards,
Harsh
Working File - Harsh.pbix (661.1 KB)
2 Likes
Thank you
And thanks to everyone who left comments. All options lead to solutions.
Grateful!
Hello @upwardD,
You’re Welcome. 
We’re glad that we’re able to help you and you found it useful.
Thanks and Warm Regards,
Harsh
Hi @upwardD , did the response provided by the experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
Thank you
But I am not so sure of the answer. I have attached a sample of the .pbi file and will be grateful for solutionsSalesData.pbix (396.3 KB)
Hello @upwardD,
In order to avoid the future date calculations where there’s no Sales below are the formulas provided alongwith the screenshot of the final results for the reference -
Step 1: Calculate Total Sales
In the file it was observed that you’ve used the naked column from the data table in order to calculate the “Total Sales”. I suggest that always convert them into the measure so that they can also be used for building secondary measures or for measure branching technique. Like I’ve used in my case -
Total Sales = SUM( Sales[SalesAmount] )
Step 2: Calculate Overall Average Sales
Overall Average Sales =
VAR OverallAverageSales =
AVERAGEX ( ALL ( Sales ) ,
Sales[SalesAmount] )
RETURN
IF( ISBLANK( Sales[Total Sales] ) ,
BLANK() ,
OverallAverageSales )
Step 3: Calculate Projections
Projection =
IF( ISBLANK( [Total Sales] ) ,
BLANK() ,
2500 )
So now let’s compare the results between earlier one with the recently obtained one.
Earlier Results -
Newly Obtained Results -
I’m also attaching the working of the PBIX file for the reference. Also providing a link below about to calculate the totals only upto specific dates.
Hoping you find this useful and meets your requirements that you’ve been looking for.
Thanks and Warm Regards,
Harsh
SalesData - Harsh.pbix (364.8 KB)
2 Likes
Apply filter on Filter Pane
Click on the table visual and Select sales amount is not blank in Filter Pane apply filter.
SalesData.pbix (395.8 KB)