cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular 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
Highlighted
Regular 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.

View solution in original post

6 REPLIES 6
Highlighted
Regular 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!!

 

 

 

 

Highlighted
Community Support
Community Support

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.
Highlighted
Regular Visitor

Re: Calculate Movement between datasets from different dates

Thank you - I'll have a look.

Highlighted
Community Support
Community Support

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.
Highlighted
Regular 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.

View solution in original post

Highlighted
Community Support
Community Support

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.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors