cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Datagulf
Resolver I
Resolver I

Additional value Non respective of Date Hierarchy

Hi community,
I have a table as follows

CompanyBilled Amount
Company A123,500
Company B342,980
Company C321,900
Company D194,500
Company E231,895
Company F349,790
Company G208,740


I am calculating Revenue in the following way with respect to the companies and time

 

Revenue TOTAL = CALCULATE(SUM(TableName[BilledAmount])

 

The forecast for each month is 80% of the Revenue Total +1,000,000 and the formula is as follows

 

Forecast = 
var __revenue =
[Revenue TOTAL]
return 
if( __revenue<>BLANK(), (__revenue *0.8) + 1000000 )

 

So I have plotted these two measures as lines in a graph and added a date hierarchy in the X-axis (shared axis).  The formula works well when drilled down to months, but when drilled to quarters, it only adds one million as shown in the formula instead of three million in the three months that make a quarter. 
This also does not reflect well when drilled to years too, which should add six million, the number of months passed in the financial quarter bearing the fact that the financial year starts from April. (April - Sept = 6 months gone)

If there is a way to make this  Forecast Measure formula more dynamic and reflect well when drilled down to different Time frames(year, Quarter, month, weeks, days) please assist. 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Datagulf , Try like

Forecast = 
var __revenue =
[Revenue TOTAL]
return 
if( __revenue<>BLANK(), (__revenue *0.8) + 1000000* distinctcount(Date[Month year])  )
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Datagulf , Try like

Forecast = 
var __revenue =
[Revenue TOTAL]
return 
if( __revenue<>BLANK(), (__revenue *0.8) + 1000000* distinctcount(Date[Month year])  )
Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

@amitchandak This works but what happens when my data surpasses 12 months?

Hi, @Datagulf 

 

According to Amit's formula, if the data surpasses 12 months, it will increase by one million times the total number of months in different years.
If his formula is helpful to you, then please consider Accept it as the solution to help the other members find it more quickly.Thank you.

 

 

Best Regards,
Community Support Team _ Zeon Zheng

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!