Forum Discussion

mancunian's avatar
mancunian
Frequent Visitor
9 years ago

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...
BhaveshPatel's avatar
BhaveshPatel
Super User
9 years ago

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

mancunian's avatar
mancunian
Frequent Visitor
9 years ago

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's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    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's avatar
      mancunian
      Frequent Visitor
      9 years ago

      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