cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors