Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PowerBIWhat
Helper I
Helper I

Week to Date - date filtering

Hi,

 

I have a dataset of sales data which is automatically refreshed from a backup at 00:30 every morning.

 

In Power BI I have a table which shows the week to date sales numbers and associated data points. Some of these data points are in advance and so the latest date of the dataset will always be the day of the backup. E.g. Backup on the 3rd Feb 2020 at 00:30, the latest date will be 3rd Feb 2020.

Herein lies the problem, in the Week to Date table when the backup takes place on a Monday. The WTD table will show data for the Week starting on Monday. This means that you never see the complete Weeks data in the WTD table. On a Sunday morning backup, you will see sales data for Mon-Sat and then on a Monday morning backup, you will see the sales data for Monday.

The filter for the table I was using is Top N (1) on the latest date in the dataset. To try to force the WTD table to look at the previous WTD on a Monday. I created a column in the dataset which was date -1. I then replaced the latest date with date-1 in the filter. In the dataset, this now means the latest date on a Monday is the Sunday but the filter doesn't seem to be working.

 

I can't change the timing of the backup.

 

 

 

Any help would be appreciated.

1 REPLY 1
v-juanli-msft
Community Support
Community Support

Hi @PowerBIWhat 

First i need to confirm my understanding abount your problem with you:

your data source has a backup time, automatically refresh from Power BI would get this backup time as the lastest time.

for example, today 2020/2/5, it is Wednesday, backup time is 2020/2/5,

after refresh, the lastest time in power bi is 2020/2/5,

then you want a table to show last week data, it is from 2020/1/27-2020/2(Monday-Sunday).

Is my understanding correct?

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors