% of Change not working with Filter

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