Forum Discussion
How to create a Dynamic chart showing weekly sum based on start and end date
I have a dataset of marketing campaigns like below. I want to create a bar chart showing the volume booked every week (week commencing Monday) based on the start and end dates.
For example, for the week commencing 2/9/24, the volume will be 180 (total for row 2 to 4); For week commencing 9/9/24, volume will be 240 (total for row 4 to 6 only, row 2 and 3 excluded as they ended on 8/9/24 already). I would also want the chart to change dynamically with slicers including 'channel', 'inventory type', 'category' and so on. Thanks!
Campaign Data:
3 Replies
- lbendlin
Super User
Can you guarantee that Start Date is always a Monday and End Date is always a Sunday? How should the volume be counted if they are not?
Most of the time they would, but sometimes not. I'm considering two potential solutions to address this:
1. Change all non-Monday start date to the prior Monday date.
2. Use a calculation that only sums the volume if the start date is on or before the Week Commencing Date (Monday), and the end date is after this date. This formula works in Excel, but I'm not sure how to replicate it in PowerBI.
I'm not very good at DAX formulas, so not sure how to do any of these in powerBI. If you have any suggestions or know a better approach, I would really appreciate it. Thanks.
- lbendlin
Let's start with the data you have and then you can think about the corner cases and exceptions.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
