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

like for like calculation

good morning guys, Apologies if I've posted in wrong place. I am new to this forum and hope to participate in long term well. I am trying to bring our live performance dashboard to power BI to we can display on big monitors. However the problem I am having is that how do I only add this bookings Vs LY bookings only for the same period. i.e if we want to show our year on year growth for total bookings at 2pm, then it should only add data from last year upto 2pm and ignore the rest of the data. I hope I have explained well. Please let me know if you require any more info? thanks in advance for help. 

4 REPLIES 4
BhaveshPatel Super Contributor
Super Contributor

Re: like for like calculation

You should post the sample data, data model and expected output to define the complete picture.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
mancunian Frequent Visitor
Frequent Visitor

Re: like for like calculation

Hi Bhavesh, Many thanks for the heads up. Please find the file attached. I have also attached a chart that I normally create on excel but would like to create on power bi. The issues I have are listed below: 

 

1 . I am not sure how I can ask power bi to look at only like for like time bucket throughout the day when calculating yoy growths. 

2. I have three year's data but the function I am aware of is calculate([Total Revenue], samepriodlastyear([dateextracted])). but I am not sure how do I split all three years from total revenue and then create 3 bars on chart to show (for 2014, 2015 and 2016). then add yoy growths for current and last year. 

3. I have saparated timestamp into date, time_buckets for 2 hours but when I drop the hour bucket into h-axis, it doesn't work. 😞

 

Please find the link and chart to the file below. 

 

https://1drv.ms/u/s!An7xL0sD_E7PhWU4DD9ac6oveczL

 

Many thanks for help in advance. 🙂 

 

 

Best regards,

Jed

 

derby_chart.png

v-yulgu-msft Super Contributor
Super Contributor

Re: like for like calculation

Hi @mancunian,

 

1. How to split all three years from total revenue and then create 3 bars on chart to show (for 2014, 2015 and 2016).

    As is shown from the image below, add measure [Total revenue] into Column calues, add column [Year] into Column series. 

    6.PNG

 

2.  Calculate yoy growths.

   

 Please refer to below measures:

 

Revenue 2014 = CALCULATE([Total revenue],FILTER(Sheet1,Sheet1[Year]=2014))
Revenue 2015 = CALCULATE([Total revenue],FILTER(Sheet1,Sheet1[Year]=2015))
Revenue 2016 = CALCULATE([Total revenue],FILTER(Sheet1,Sheet1[Year]=2016))

2015 YoY Revenue = divide([Revenue 2015]-[Revenue 2014],[Revenue 2014])
2016 YoY Revenue = divide([Revenue 2016]-[Revenue 2015],[Revenue 2015])

   

Add measure [2015 YoY Revenue] and [2016 YoY Revenue] into Line values.

7.PNG

 

     

If you still have any question, please feel free to ask.

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
mancunian Frequent Visitor
Frequent Visitor

Re: like for like calculation

Hi Yuliana, Many thanks for this. It's very helpful in terms of showing yoy growths for last three for the past priod (i.e up until yesterday's date). However I have another issue, which is related to Live (today's) performance. This means that if I want to show that up untill 2pm, we have grown 35% YoY. How do I get Power BI to look only up untill 2pm data (for 2016, 2015, and 2014) and then give me YoY figures based on that. 

 

Many thanks,

J

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 171 members 1,717 guests
Please welcome our newest community members: