Forum Discussion
Sum last week by time
Hi guys,
I'm trying calculate the sales from last week by time and Week Day,
If today is Sunday on 10AM, I need know how much we sell on last Sunday on 10AM.
Example:
TIME TODAY LAST WEEK ON THIS DAY WEEK
12PM 1000 2000
13PM 1350 1000
TOTAL: 2350 3000
To calulate time, I created an calculate columm like this TIME = HOUR(dDate)
To calulate sales from today, I created:
Hi Anonymous ,
Try to modify your measure as below :
LAST WEEK = CALCULATE(SUM(dBase[SALES]), FILTER(ALL(dBase), [Week Number] = SELECTEDVALUE(dBase[WEEK NUMBER])-1 && [Week day] = SELECTEDVALUE(dBase[WEEK DAY])&& [TIME] = SELECTEDVALUE(dBase[TIME])), FILTER(ALL(dBase), dBase[DATE] = TODAY()-7))
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
3 Replies
- Greg_Deckler
Community Champion
Anonymous - Wouldn't your last filter be TODAY() - 7?
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2. - amitchandak
Super User
Anonymous , You can try what Greg_Deckler suggested.
Also you have week behind measure
Example
Week behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-7,DAY)) //same way last year same week day Year Week behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))For WOW refer to my blog: https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos. Hi Anonymous ,
Try to modify your measure as below :
LAST WEEK = CALCULATE(SUM(dBase[SALES]), FILTER(ALL(dBase), [Week Number] = SELECTEDVALUE(dBase[WEEK NUMBER])-1 && [Week day] = SELECTEDVALUE(dBase[WEEK DAY])&& [TIME] = SELECTEDVALUE(dBase[TIME])), FILTER(ALL(dBase), dBase[DATE] = TODAY()-7))
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
