cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
po Member
Member

Previous value for same day last week and last week

Hi,

 

We have a sales table which will have many weeks worth of data in it

 

YearWeek

 

Shop

Day

Amount Supplied

Amount Sold

 

and wish to show for each day of week the amount sold for the corresponding day of the previous week

when view at day level

YearWeek  Shop   Day    Amount Sold  Previous Amount Sold

201901       A        SUN     100                50

 

Similarly when view at week level wish to show the amount sold for the previous week.

 

How is this best achieved?

 

Have used below in past but this not working here and wonder if better approach

 

calculate(SUMX(sales,sales[amount sold]) ,filter(all
(calendar),calendar[Date]=MAX(calendar[Date])-7) )

 

When use above just shows a value for one week - other weeks blank.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Previous value for same day last week and last week

I think you want:

 

Measure = 
VAR __max = MAX(calendar[Date])
RETURN
calculate(SUMX(sales,sales[amount sold]) ,filter(all
(calendar),calendar[Date]=__max-7) )

If you have the week in the visual and this, then the first line should be context constrained to the row.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Previous value for same day last week and last week

If you want to do this as a column you might look into EARLIER. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

 

Also see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

 

Finally, you might want to use Sequential to make the math easier: https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

po Member
Member

Re: Previous value for same day last week and last week

Thanks for reply ande useful linkss.

 

Finding the fromula below works when have a slicer aqnd selecting a week will calculate the value relative to that previous week but issue have is if want to say have a few weeks showing at once below will only show one value as the max of the latest date shown is used.

 

e.g if 201904 will show 201903 but 201902 and 201901 will be blank. 

 

calculate(SUMX(sales,sales[amount sold]) ,filter(all
(calendar),calendar[Date]=MAX(calendar[Date])-7) )

Super User
Super User

Re: Previous value for same day last week and last week

I think you want:

 

Measure = 
VAR __max = MAX(calendar[Date])
RETURN
calculate(SUMX(sales,sales[amount sold]) ,filter(all
(calendar),calendar[Date]=__max-7) )

If you have the week in the visual and this, then the first line should be context constrained to the row.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

po Member
Member

Re: Previous value for same day last week and last week

Thansk - yes this is what I thought.  However, when do this if have say weeks 201901 to 201904 see that the row with 201903  is populated (as has max date-7) assuming in week 201904 but other rows are blank for previous week value.

po Member
Member

Re: Previous value for same day last week and last week

Looks to be working now not sure why wasn't before 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

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: 6 members 1,040 guests
Please welcome our newest community members: