like for like calculation

[mancunian]

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. 

[BhaveshPatel]

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

[mancunian]

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

[v-yulgu-msft]

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. 

    

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.

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

Best regards,
Yuliana Gu

[mancunian]

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