cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filter Table visual with orders from today and next 5 days

I have a PowerBI report that displays 5 table visuals with data. I've tried different ways to get this to work, without any luck and ran out of ideas.

Basically I'm using a view from Sql server and the dataset returned contains around 1000 rows. Each table visual used gets filtered  based on the date the order is closing.
So Table 1 will show orders closing today, Table 2, closing tomorrow, Table 3 closing the following date,etc.

The main issue revolves around that we don't close orders every day and the goal is to show data for each day we have closings. 

On the table posted below I control the data each table is showing by setting the filter individually. For example first table must always show today's closings. So the filter applied is "Myday1 =1", which comes from the view using this code:

 

MyDay1 = CASE
WHEN closing_date = CONVERT(DATE, GETDATE(), 101)
THEN 1
ELSE 0
END,

MyDay2 = CASE
WHEN closing_date > CONVERT(DATE, GETDATE(), 101)
AND DATEDIFF(d, CONVERT(DATE, GETDATE(), 101), closing_date) = 1
THEN 1
ELSE 0
END,

 

Is they way to control the filter only where there is data for any specific date?

 

ClosingDBScreen.png

 

SampleTable.PNG

 

5 REPLIES 5
Highlighted
Super User IV
Super User IV

Re: Filter Table visual with orders from today and next 5 days

Do you date the table to identify the Working date? Assume that part of date table.

 

Working Day = Maxx(Filter(Date, Date[Date]<=Today(), Date[Working]= "Y"),Date[Date])

Next Working Day = Minx(Filter(Date, Date[Date]>Date[Working Day], Date[Working]= "Y"),Date[Date])

 

You can have flags on these dates. and filter them on you visuals



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Community Support
Community Support

Re: Filter Table visual with orders from today and next 5 days

Hi @dalabera , 

 

I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

Highlighted
Frequent Visitor

Re: Filter Table visual with orders from today and next 5 days

@amitchandak 

Well, my requirements is not to exclude weekends, however I'm trying to use your approach and still not working for me.

 

I created a custom table that have all the dates with data. My goal is to show only 5 grids or tables populates with the datest starting from today. Let's assume the current day always have data, then I just need to show the next 4 days of data.

 

DayswithData = SUMMARIZE(MyData, MyData[ClosingDate],"JobCount#", COUNTROWS(MyData))

 

Highlighted
Frequent Visitor

Re: Filter Table visual with orders from today and next 5 days

@dax My requirement is to show 5 grids or table with data. I have accomplish this by starting from today and creating filters for the next following 4 days. The issue is that there are times in the week that there is no data and I end up showing nothing.

The solution will be to show data in the grid if there is data for the next day which has data. That was I was trying to explain the table of my original post.

Highlighted
Super User IV
Super User IV

Re: Filter Table visual with orders from today and next 5 days

If you have date table try using rolling formula

Rolling 5 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),5,DAY)) 
Rolling 5 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),5,DAY))  

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @

Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors