Progression Sum with existing and non existing dates

[Andiko]

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

[v-xinruzhu-msft]

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.

[Andiko]

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:

[Andiko]

@shafiz_p, do you think you can take a look at this, please? -I am having the same problem with the dates again

[shafiz_p]

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

[Andiko]

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:

[shafiz_p · Accepted 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!!