Forum Discussion
Helper IHow to calculate the difference between 2 weeks
Hi yall!
Im having an issue with this measure, what im trying to do is: If the column Week in my week table is filtered, then substract 1 to see the sales from the week before so I can compare the sales from the week in course with the past week,
But when I try to add the -1 to the filter formula it doenst show anything
Anyone have an Idea why is this happening?
Thanks in advance!
Hi JoseLuisAGZZ ,
The formula '[date column]=selectedvalue[date column]' returns an equation with 'current line date' = date two weeks ago, which returns false. So the measure return null values.
You can try Dateadd function.
Try measure as:
Measure = CALCULATE( SUM(Facturacion[FACTURACION_SEMANA], FILTER( DATE_ok, DATEADD(Date_OK[SEMANA],-14,DAY) ) )If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
2 Replies
- amitchandak
Super User
2 week from date selected ?
one of the ways is with date table
2 weeks behind sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-14,DAY)))
With Week rank column in Date
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
Last 2 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-2 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Last 2 weeks before 2= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])-3))
More on the week and date table
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8 - v-xulin-mstf
Community Support
Hi JoseLuisAGZZ ,
The formula '[date column]=selectedvalue[date column]' returns an equation with 'current line date' = date two weeks ago, which returns false. So the measure return null values.
You can try Dateadd function.
Try measure as:
Measure = CALCULATE( SUM(Facturacion[FACTURACION_SEMANA], FILTER( DATE_ok, DATEADD(Date_OK[SEMANA],-14,DAY) ) )If you still have some question, please don't hesitate to let me known.
Best Regards,
Link
Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!
