Calculate Movement between datasets from different dates

[lmacn]

I have previously created a weekly sales report in excel - using pivot tables.

I also created a static "movement" tab by copying and pasting the previous week's figures next to the current week, and calculating the difference by using a straight forward excel calculation in the static data.

I am trying to recreate this in PowerBI.

I have 2 datasets - one from 14th Dec and one from 21st Dec.

The datasets are exactly the same - just pulled on different dates.

I have also created a DateKey table.

I need to show:

YTD 2016 as at 14th Dec vx YTD 2017 as at 14th Dec

YTD 2016 as at 21st Dec vx YTD 2017 as at 21st Dec

I ALSO need to show:

Movement from 14th - 21st 2016

Movement from 14th - 21st 2017

I've tried variance and difference and subtraction quick measures but none are returning the correct figures.

I'm not sure how to establish the correct relationships between the 14th and 21st Datasets.

Any advice very welcome!!

[v-chuncz-msft]

@lmacn,

You may take a good look at the following article.

https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

[lmacn]

Thank you - I'll have a look.

[v-chuncz-msft]

@lmacn,

Any progress, feel free to share with us.

[lmacn · Accepted solution]

I resolved this by setting up a reference table with the names of all the products and a reference date table.

I was then able to compare the 2 datasets from the different dates by liking the booking date to the date table and the product name to the product reference table.

[v-chuncz-msft]

@lmacn,

Glad to hear that. You may help accept the solution above. Your contribution is highly appreciated.

[lmacn]

I have previously created a weekly sales report in excel - using pivot tables.

I also created a static "movement" tab by copying and pasting the previous week's figures next to the current week, and calculating the difference by using a straight forward excel calculation in the static data.

I am trying to recreate this in PowerBI.

I have 2 datasets - one from 14th Dec and one from 21st Dec.

The datasets are exactly the same - just pulled on different dates.

I have also created a DateKey table.

I need to show:

YTD 2016 as at 14th Dec vx YTD 2017 as at 14th Dec

YTD 2016 as at 21st Dec vx YTD 2017 as at 21st Dec

I ALSO need to show:

Movement from 14th - 21st 2016

Movement from 14th - 21st 2017

I've tried variance and difference and subtraction quick measures but none are returning the correct figures.

I'm not sure how to establish the correct relationships between the 14th and 21st Datasets.

Any advice very welcome!!