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
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

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!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors