Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi community,
I have a table as follows
Company | Billed Amount |
Company A | 123,500 |
Company B | 342,980 |
Company C | 321,900 |
Company D | 194,500 |
Company E | 231,895 |
Company F | 349,790 |
Company G | 208,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.
Solved! Go to Solution.
@Datagulf , Try like
Forecast =
var __revenue =
[Revenue TOTAL]
return
if( __revenue<>BLANK(), (__revenue *0.8) + 1000000* distinctcount(Date[Month year]) )
@Datagulf , Try like
Forecast =
var __revenue =
[Revenue TOTAL]
return
if( __revenue<>BLANK(), (__revenue *0.8) + 1000000* distinctcount(Date[Month year]) )
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |