Week to Date - date filtering

[PowerBIWhat]

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.

[v-juanli-msft]

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.