Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello!
I have a Meassure called [Total Heads Serv Grow] which I need it to:
1. Take the [Actual SR] value from the 'Input Data' Table. This would be named ActualSR which represents the actual Number of People I have now.
2. Then, every month I need to bring new people in at a steady rate. This rate is given by the MonthlyMultiplier.
This means that if Today I have 10 people and the MonthlyMultiplier is 10%, then the Next Month I would have (10 * 1.10^1 =) 11 People and in 2 months I would have (10 * 1.10^2 =) 12.1 People and so on and so forth
3. This should be calculated for the Dates that exist in the InputDB ONLY. This entries are reflected in the [Cumulative Recurring Revenue] column
** In case there is no entry in the InputDB for a given month, then the formula should still calculate it and assign the date of the 1st day of that month.
*** The end goal is to have a progresion of the number of people that has to be brough in.
**** The calculation should be run until the latest date that is contained in the InputDB
I have constructed this using the formula below:
=VAR ActualSR = CALCULATE(
SUMX( 'Input Data','Input Data'[Actual SR]),
FILTER(
ALLSELECTED(InputDB),
InputDB[Close_Date] = TODAY()
)
)
VAR MonthsPassed = DATEDIFF(TODAY(),MAX('Calendar'[Date]),MONTH)
VAR MaxDateInputDB = CALCULATE(MAX(InputDB[Close_Date]), ALLSELECTED(InputDB))
VAR MonthlyMultiplier = 1+ (AVERAGEX(
'Input Data', 'Input Data'[Annual Headcount Growth Rate])
)/12
Return
IF(
MAX('Calendar'[Date]) >= TODAY() &&
MAX('Calendar'[Date]) <= MaxDateInputDB,
CEILING(ActualSR * POWER(MonthlyMultiplier,MonthsPassed),1),
BLANK()
)
The [Total Heads Serv Grow] is giving me the correct values. But the problem is that, as you can see in the image below, it is giving me values for all the dates from the Calendar Table (even if they do not exist in the InputDB).
Also, I have been able to add the condition for running the calculation until the latest date contained in the InputDB. However, I have not been able to add the filter for the case in where there is no entry in the InputDB for a given month.
Furthermore, the formula is taking a lot of computation power and I believe is not properly done. Thus, a better and more efficient way would be also valuable.
I have tried many options using the EDATE function, SUMX, SUM, Etc. But, either the values are incorrect, or does not solve the problem with the dates.
Nothing seems to work. Thus, I would like to get your help please!
I am attaching the file I am using here: Test-File 2
Solved! Go to Solution.
You are using country level from InputDB as a slicer to propagate. But relationship goes downhill from 'Input Data' to InputDB. See image :
aggregating from upper table but trying filter from down table without defining relationship is not possible. Use country level as a slicer from 'Input Data' table. See image below for changes after using country level from 'Input Data' table:
Suggesting you to pay attention to your relationship and how they working.
Thanks!!
Hi @Andiko
You can try the following measure.
MEASURE =
VAR ActualSR =
CALCULATE (
SUMX ( 'Input Data', 'Input Data'[Actual SR] ),
FILTER ( ALLSELECTED ( InputDB ), InputDB[Close_Date] = TODAY () )
)
VAR MonthsPassed =
DATEDIFF ( TODAY (), MAX ( 'Calendar'[Date] ), MONTH )
VAR MaxDateInputDB =
CALCULATE ( MAX ( InputDB[Close_Date] ), ALLSELECTED ( InputDB ) )
VAR MonthlyMultiplier =
1
+ ( AVERAGEX ( 'Input Data', 'Input Data'[Annual Headcount Growth Rate] ) ) / 12
RETURN
IF (
SUM ( 'InputDB'[Cummulative Recurring Revenue] ) <> BLANK (),
IF (
MAX ( 'Calendar'[Date] ) >= TODAY ()
&& MAX ( 'Calendar'[Date] ) <= MaxDateInputDB,
CEILING ( ActualSR * POWER ( MonthlyMultiplier, MonthsPassed ), 1 )
)
)
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-xinruzhu-msft, thank so much for your help!
I was able to solve the problem with the dates. Now, I am having problems with the actual values as you mentioned. The total sum is fine when there is no filter on the Country_Level.
However, when I activate the filter (choose one country), the numbers do not change. This should not be like that as it should work dynamically (please refer to the images attached).
Could you please take a look for me?
Here is the File with the Updated formula: Test File
Formula:
VAR ActualSR = SUMX( 'Input Data', 'Input Data'[Actual SR]) VAR MonthsPassed = DATEDIFF(min('Input Data'[Date]),MAX('Calendar'[Date]),MONTH) VAR MaxDateInputDB = CALCULATE(MAX(InputDB[Close_Date]), ALL(InputDB)) VAR MonthlyMultiplier = 1+ (AVERAGEX('Input Data', 'Input Data'[Annual Headcount Growth Rate]))/12 Return IF( MAX([Close_Date]) >=min('Input Data'[Date]) && MAX([Close_Date]) <= MaxDateInputDB, CEILING(ActualSR * POWER(MonthlyMultiplier,MonthsPassed),1), BLANK() )
All filters selected:
- One Filter Selected:
@shafiz_p, do you think you can take a look at this, please? -I am having the same problem with the dates again
Hi @Andiko , I can see that head count is 123 for month october. I have tried to find out manually. There is relationship between input data and inputDB using Country level. Your ActualSR for current Date which is 10/11/2024 should be 137. See Image below:
For country level B and D, I have found head count in Input Data :
Try below code (If close date present in calendar table, then do calculation otherwise not) :
Total Heads Serv Grow=
VAR ActualSR = CALCULATE(
SUMX('Input Data', 'Input Data'[Actual SR]),
FILTER(
ALLSELECTED(InputDB),
InputDB[Close_Date] = TODAY()
)
)
VAR MonthsPassed = DATEDIFF(TODAY(), MAX('Calendar'[Date]), MONTH)
VAR MaxDateInputDB = CALCULATE(MAX(InputDB[Close_Date]), ALLSELECTED(InputDB))
VAR MonthlyMultiplier = 1 + (AVERAGEX(
'Input Data', 'Input Data'[Annual Headcount Growth Rate]
) / 12)
VAR HasCloseDate = CALCULATE(
COUNTROWS(InputDB),
FILTER(
ALLSELECTED(InputDB),
InputDB[Close_Date] = MAX('Calendar'[Date])
)
) > 0
RETURN
IF(
HasCloseDate,
IF(
MAX('Calendar'[Date]) >= TODAY() &&
MAX('Calendar'[Date]) <= MaxDateInputDB,
CEILING(ActualSR * POWER(MonthlyMultiplier, MonthsPassed), 1),
BLANK()
),
BLANK()
)
Hope this helps!!
Best Regards,
Shahariar Hafiz
Hi @shafiz_p, thank so much for your help!
I was able to solve the problem with the dates. Now, I am having problems with the actual values as you mentioned. The total sum is fine when there is no filter on the Country_Level.
However, when I activate the filter (choose one country), the numbers do not change. This should not be like that as it should work dynamically (please refer to the images attached).
Could you please take a look for me? 🙏
Here is the File with the Updated formula: Test File
Formula:
VAR ActualSR =
SUMX( 'Input Data', 'Input Data'[Actual SR])
VAR MonthsPassed = DATEDIFF(min('Input Data'[Date]),MAX('Calendar'[Date]),MONTH)
VAR MaxDateInputDB = CALCULATE(MAX(InputDB[Close_Date]), ALL(InputDB))
VAR MonthlyMultiplier = 1+ (AVERAGEX('Input Data', 'Input Data'[Annual Headcount Growth Rate]))/12
Return
IF(
MAX([Close_Date]) >=min('Input Data'[Date]) &&
MAX([Close_Date]) <= MaxDateInputDB,
CEILING(ActualSR * POWER(MonthlyMultiplier,MonthsPassed),1),
BLANK()
)
All filters selected:
- One Filter Selected:
You are using country level from InputDB as a slicer to propagate. But relationship goes downhill from 'Input Data' to InputDB. See image :
aggregating from upper table but trying filter from down table without defining relationship is not possible. Use country level as a slicer from 'Input Data' table. See image below for changes after using country level from 'Input Data' table:
Suggesting you to pay attention to your relationship and how they working.
Thanks!!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
42 | |
28 | |
23 | |
22 | |
22 |