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
Datagulf
Responsive Resident
Responsive Resident

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])  )

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])  )

@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
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.