Forum Discussion
Cumulative weekly kpis
Hi,
I am trying to get weekly cumulative traffic data for a chart that I am creating. For that, I am using this formula:
As you can see in the following screenshot, it does the cumulative for the weeks it has data but then it adds the total cumulative number for that traffic KPI. there's only one filter used to filter that table (#1: Book 1_ASIN). The results highlighted in #2 are indeed doing as expected: cumulating the traffic week on week. What I don't understand is why the total traffic aggregated is showing up in #3 and #4.
Not sure if this is coincidental but the error happens when there are negative numbers in the Week number (this is correct as it's relative to a release date and there can be pre-orders).
That "week number" is coming from a different table made out of this formula:
Weeks = GENERATESERIES(-50,1000)
This is the relationships created between the tables (the calendar table is not used for this precise exercise but for another chart):
Thanks in advance for your help!
3 Replies
- amitchandak
Super User
Alvarjo , for WOW and WTD refer my blogs of video
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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145sThanks amitchandak . I have checked your videos and links but I am not sure how that answers my question.
The week number does not come from a calendar table. It's relative to PubDate (publication date), which is different for each ASIN (the products identifiers) and it derives from this formula:
week post pubdate = ceiling(('DE Traffic Weekly_1'[ReportDate]-('DE Traffic Weekly_1'[PubDate].[Date]))/7,1)
And this is how the tables have been related:
It'd be great if you can help me figure out why the cumulating figures are showing up in weeks when I have no data.
Thanks in advance
Nevermind. Found the solution. For those interested:
Book 1_Cumulative.Traffic =if(countrows(FILTER('DE Traffic Weekly_1','DE Traffic Weekly_1'[week post pubdate]=SELECTEDVALUE(weeks[Week Post Pubdate])))=0,BLANK(),CALCULATE(sum('DE Traffic Weekly_1'[Glance views]),filter(ALLSELECTED('DE Traffic Weekly_1'),'DE Traffic Weekly_1'[week post pubdate]<=SELECTEDVALUE(Weeks[Week Post Pubdate]))))
