Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Nevermind. Found the solution. For those interested:
@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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
Time 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=145s
Thanks @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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
101 | |
84 | |
79 | |
69 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |