cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lmacn Frequent Visitor
Frequent Visitor

Calculate Movement between datasets from different dates

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!!

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
lmacn Frequent Visitor
Frequent Visitor

Re: Calculate Movement between datasets from different dates

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.

6 REPLIES 6
lmacn Frequent Visitor
Frequent Visitor

Calculate Movement between datasets from different dates

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!!

 

 

 

 

Community Support Team
Community Support Team

Re: Calculate Movement between datasets from different dates

@lmacn,

 

You may take a good look at the following article.

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lmacn Frequent Visitor
Frequent Visitor

Re: Calculate Movement between datasets from different dates

Thank you - I'll have a look.

Community Support Team
Community Support Team

Re: Calculate Movement between datasets from different dates

@lmacn,

 

Any progress, feel free to share with us.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lmacn Frequent Visitor
Frequent Visitor

Re: Calculate Movement between datasets from different dates

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.

Community Support Team
Community Support Team

Re: Calculate Movement between datasets from different dates

@lmacn,

 

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.