Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mancunian
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
Community Champion
Community Champion

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.

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

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.