% of Change not working with Filter

[utsavlexmark]

Hello,

I am working on a dashboard for our Paid Search Campaign. Different campaigns are having different ID and the datasource containing data for each and every campaign ID.

I am trying to set up an graph to show WoW changes in conversion (Order and Revenue).

Created Mesure for that and that is working perfectly for over all data. But when I am trying to filter it with the campaign ID - it returns wrong value.

The Mesures I have created are:

1. Total Order = SUM('Sheet1 (2)'[Orders])

2. Previous Week Oder =
CALCULATE('Sheet1 (2)'[Total Order],
FILTER(ALL('Sheet1 (2)' ),
'Sheet1 (2)'[Week] = SELECTEDVALUE('Sheet1 (2)'[Week]) - 1 &&
'Sheet1 (2)'[Year] = SELECTEDVALUE('Sheet1 (2)'[Year])))

3. WoW % Change in Order = DIVIDE('Sheet1 (2)'[Total Order],'Sheet1 (2)'[Previous Week Oder],0)-1

These 3 mesures are working perfectly in general condition, but when I am filtering with Campaign IDs

Mesure 1(Total Order) is working but Mesure 2(Previous Week Oder) is not - as a result Mesure 3 (WoW % Change in Order) is showing wrong data.

Any help?

Regards

Utsav

[TomMartens · Accepted solution]

Hey,

from looking at your DAX Statement for your measure "Previous Week Order" you use

ALL('Sheet1 (2)')

This ALL statement also "resets" the filtering of the underlying data to ALL campaigns, use this instead
ALL(
'Sheet1 (2)'[Week]
,'Sheet1 (2)'[Year]
)

Hopefully this is what you are looking for

Regards,

Tom

[utsavlexmark]

Hey Tom,

I would like to ask you a question that is relavant with this issue. Currently I am working with 2018 data only - but in next year the Week Numbers will be repeted and I guess that time I need to modify the DAX Statement to avoid the duplication issue.

Can u suggest something on this?

Regards

Utsav

[TomMartens]

Hey,

here you will find a PBIX file.

This file contains two tables "Fact" and "Calendar M". The table Fact represents your table and the table Calendar M is a separate Calendar table.

There are 2 different approaches how/where to create the Calendar table, namely PowerQuery/M and DAX. Personally I'm using Power Query and M to create my Calendar table, this allows to reference the Calendar table from other Power Query queries during the data preparation phase. In this example I'm using a Merge Join to create the Date colum in the Fact table.

Basically the pbix works like this:

1. Create a Calendar table
   1. Date
   2. Year
   3. Week
   4. StartOfWeek
   5. YearWeekNumber
   6. YearWeekNumber Index
2. Adjust Fact table
   1. Create YearWeeknumber column
   2. Create Date Column using a Merge Join based on the column YearWeekNumber

Basically the creation of the Calendar table using Power Query / M is straightforward. The column "YearWeekNumber Index" is created using multiple steps (for simplicity I omitted the custom function that I'm using):

1. Grouping the table by column "YearWeekNumber" - Grouping Operation "All Rows"
2. Sorting the table
3. Adding an Index Column starting with 1
4. Expanding the table

This creates a column that contains the value 1 for each day in the first week in the first year of the calendar table, and the value 2 for the 2nd week in ...

This column can be used for all calendar based caluculations that need some kind of index, e.g. "last three weeks" or "WoW -Growth".

An example is the calculation of the measure "WoW Growth".

Due to the fact, that the fact table "Fact" reflects measurements that have a granularity of weeks, but the Calendar table features a daily granularity (this is common and best practice for calendar tables), it's necessary to create a date column in the fact table.

This is done by using a merge join from the fact table to the calendar table based on the column "YearWeekNumber". Due to the fact that this would return many days I decided to return the latest day of the joined week, this is done by the query step ""

= Table.AggregateTableColumn(#"Merged Queries", "Calendar M", {{"Date", List.Max, "Date"}})

After the data preparation is done, both tables can be related using the column Date.

Hopefully this gets you started.

Regards,

Tom

[utsavlexmark]

Hello Tom,

Sorry to say that this solution is too much tough for me to handle.

The logics used are difficult for me to understand.

Can you refer any other simplyfied process?

Regards

Utsav

[utsavlexmark]

Hello Tom,

I am working on your suggestion and will let you know about the result. As it is very complicated for me (as a newbie) - you can understand it will take sometime for me to understand and implement the logics.

But I have no words to Tanking You for this. I am glad that you took so much of your time and energy to help me.

Regards

Utsav

[TomMartens]

You're welcome 😀

[TomMartens]

Hey,

basically there is a solution, but it gets a little more complex,

a separate calendar table will be involved and also some PowerQuery stuff that creates a Date column in your existing table.

I will provide an example on Sunday, I'm wondering if the week reflects the ISO week within the year, this is how my existing solution handles facts that are on a weekly granularity.
I have to simplify this solution the next days.

Regards,
Tom

[utsavlexmark]

Thanks Tom,

It is working perfectly now. Within the filter function I have mentioned (as suggested by you) that Week and Year needs to be filter only.

Thanks again.

Regards

Utsav