cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Budfudder Member
Member

Comparing Estimate To Actual - Using Two Dates

We have a series of opportunities that represent possible deals, and each has an estimated value and an estimated close date. So at any time I can look at the open opportunities, their estimated values and estimated close dates and say "Okay, on June 1st (for example), we should have $234,153 in sales". Then, on June 1st, I can go and look at the actual value of all the opportunities closed and compare the estimated with the actual. The estimated value and the actual value fields are, of course, different fields.

 

So I need to be able to select two dates (or a date range) and say "Give me the estimated income shown on the first date...and the actual income shown on the second date". 

 

I've been trying to use a date slicer and manually get the first and last dates of the period that the user sets it to and use those as the two dates, but that doesn't work - I can't even work out how to get those dates. And it seems a really cumbersome approach anyway.

 

What's the best way to do this? I feel like I'm missing something.

4 REPLIES 4
Super User IV
Super User IV

Re: Comparing Estimate To Actual - Using Two Dates

Sample data would be very helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


That being said, take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Community Support
Community Support

Re: Comparing Estimate To Actual - Using Two Dates

Hi @Budfudder

Does your dataset look like the table below

deals	estimated value	  estimated close date	  actual value	  actual date
1	1	          1/1/2018	          2	          1/2/2018
2	2	          1/2/2018	          3	          1/3/2018
3	3	          1/3/2018	          1	          1/4/2018
4	4	          1/4/2018	          2	          1/4/2018
5	5	          1/5/2018	          3	          1/3/2018
6	6	          1/6/2018	          6	          1/7/2018

Then when you select a date range or a certain date, it should show the sum of estimated value and actual value for all deals(opportunities) ->here i'm not sure it should be all deals or for each deal?

 

Please let me know more clearly.

 

Best Regards

Maggie

 

Highlighted
Budfudder Member
Member

Re: Comparing Estimate To Actual - Using Two Dates

Hi Maggie,

 

THat's a good example of how the data looks - except that some values won't have 'actual date' at all - meaning they haven't yet closed.

 

The final figure would be the sum of all deals closed since the first date

 

So, for example, if the user chose a first date of 1/5 and a second date of 1/7, then the the value for deal 5 in your table wouldn't be included, because although it has an estimated close date of 1/5, its actual date was 1/3 - outside the period 1/5-1/7.

Budfudder Member
Member

Re: Comparing Estimate To Actual - Using Two Dates

Thanks Greg. I'm looking at the two links you gave, and they will help. But I feel the biggest problem is being able to pick off the dates the user has input.

 

How can I allow the user to select a date period and get from that selection the actual dates to use in DAX expressions?

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors