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

WEEKNUM Relative Date Filtering

Hello All,

 

I would like to filter my entire report to include all dates(weeknum's) up until the week before current.

 

E.g. I want my report to include WEEKNUMS 1 through 45 but not week 46 if that was our current week.

 

I have various graphs which need to report what has sold versus what has been dispatched for the weeks prior to this week.

 

The reason for weeks is that is the best way to group the data.

 

Information which may be helpful:

The sold table's Sold Date includes the latest date(& weeknum) which should be used in the report.

 

Ideally would like it to not be hardcoded and would filter the whole report based on the latest date which something sold.

 

Or just based on the NOW() time and go back one week as the report will only be relevant for that week until new data is imported.

 

This is only my second post here so please let me know if you need any more information to help me out.

 

Thank you Smiley Happy

 

 

Relationships.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

graph.PNGFor example - I would like this to exclude those last 100%'s by only using the weeknum with sales data

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: WEEKNUM Relative Date Filtering

Hi,

 

Does this measure work?

 

Measure = if(ISBLANK(SUM(sold[Quantity Sold])),BLANK(),1-DIVIDE(SUMX(RELATEDTABLE('sold'),[Quantity Sold]),SUMX(RELATEDTABLE('Dispatched'),[Quantity Delivered]),0))

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
Super User
Super User

Re: WEEKNUM Relative Date Filtering

Hi,

 

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
WillEyedowin Frequent Visitor
Frequent Visitor

Re: WEEKNUM Relative Date Filtering

Hi,

 

Appreciate the offer - However, unfortunately, there is some sensitive data in the report.

 

Is there an easy way to anonymize/de-sensitize or limit your access to this data?

 

Further, how would you recommend I upload the file for you to download it? 

Super User
Super User

Re: WEEKNUM Relative Date Filtering

Hi,

 

Create a dummy dataset and upload the file to Google Drive or any such similar service.  Share the download link.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
WillEyedowin Frequent Visitor
Frequent Visitor

Re: WEEKNUM Relative Date Filtering

Super User
Super User

Re: WEEKNUM Relative Date Filtering

Hi,

 

I get the "Access denied" message when i click on that link.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
WillEyedowin Frequent Visitor
Frequent Visitor

Re: WEEKNUM Relative Date Filtering

Hi,

 

I changed the link to private last night as I assumed you would have already downloaded it - public again now 🙂 

Super User
Super User

Re: WEEKNUM Relative Date Filtering

Hi,

 

In the Stores Table, there is no Date column.  There should be a Date column there which should connect to the dateKey Table.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
WillEyedowin Frequent Visitor
Frequent Visitor

Re: WEEKNUM Relative Date Filtering

The Stores table is just a list of supermarkets - I don't believe a date column would work as a store does not have a date?

Highlighted
Super User
Super User

Re: WEEKNUM Relative Date Filtering

Hi,

 

Does this measure work?

 

Measure = if(ISBLANK(SUM(sold[Quantity Sold])),BLANK(),1-DIVIDE(SUMX(RELATEDTABLE('sold'),[Quantity Sold]),SUMX(RELATEDTABLE('Dispatched'),[Quantity Delivered]),0))

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 271 members 2,783 guests
Please welcome our newest community members: