Comparing previous campaigns

Hi

I’m trying to replicate a campaign comparison chart I use in Excel so that I can embed it in my Power BI reporting.

The chart is:

It’s a cumulative chart by day into the campaign (I have to use a day count because the previous campaigns weren’t necessarily on the same dates in previous years.)

I’ve imported the previous years’ data into my model. For example the 2018 data looks like this:

This year’s campaign is acitve now and is refreshed daily in my Power BI report.

Do I need to create a new table with this year’s data to get the day number and the cumulative total?

Then how do I get them all on the same chart to look like the chart above?

I tried using the line chart visualisation but got some weird results.

Thanks

@KieftyKids,

In future please supply a small sample data set - thanks in advance!

.
No, just create one table containing all your Campaign data. In Power Query you can add the Day number and Cumulative Total. That way creating a Line Chart will be straight forward :wink:

  1. Created some dummy data
    image
  2. Entered a manual Step by pressing fx in front of the formula bar (1). Power Query returned the Name of the last step (2) and wrap a Table.Buffer around that (3). This way PQ loads the table into memory just once and not repeatedly for every time it references it.
    image
  3. To add a DayNum we need to identify the number of rows where the Donatedate is smaller or equal to the Donatedate in the current Record AND where the Campaigns match.
    To do so add a Custom Column use Table.SelectRows to filter the BufferedTable (BT) and check the values against the Campaign table (CT) with a custom function. Because we want to add a CumTotal later as well I’m going to leave this nested table called TempTable in place, so I can reference it again without having to repeat the same logic all over again.
  4. Add a Custom Column for the DayNum using Table.RowCount
  5. Add a Custom Column for the CumNum using List.Sum
  6. and a Custom Column for the CumTotal again using List.Sum

Placed inside a Line Chart, it looks something like this.
image

I hope this is helpful. Here’s my sample file.
eDNA - Comparing Campaigns.pbix (37.1 KB)

4 Likes

Hi @KieftyKids, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

Hi Melissa

I finally found time to attempt your solution and it worked perfectly.

Thank you very much!

image

1 Like