Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
Andiko
Frequent Visitor

Progression Sum with existing and non existing dates

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

 

Andiko_2-1728587749201.png

 

 

 

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

 

 

 

1 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 :

shafiz_p_0-1728703843838.png

 

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:

shafiz_p_1-1728704047354.png

 

Suggesting you to pay attention to your relationship and how they working.

Thanks!!



View solution in original post

6 REPLIES 6
v-xinruzhu-msft
Community Support
Community Support

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:

Andiko_3-1728694188635.png

 

 

- One Filter Selected:

 

 

 

Andiko
Frequent Visitor

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

shafiz_p_0-1728650085059.png

 

For country level B and D, I have found head count in Input Data :

shafiz_p_1-1728650217674.png

 

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:

Andiko_0-1728693770135.png

 

- One Filter Selected:

 

Andiko_1-1728693823287.png

 

 

You are using country level from InputDB as a slicer to propagate. But relationship goes downhill from 'Input Data' to InputDB. See image :

shafiz_p_0-1728703843838.png

 

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:

shafiz_p_1-1728704047354.png

 

Suggesting you to pay attention to your relationship and how they working.

Thanks!!



Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.