Using End of Month as outer context to show active subscriptions per month

[EliasAR]

Hi everyone

I have a database with different sales opportunities, each of them has a "Service Start Date", a "Service End Date" and a Value.

Example: 

Client 1, SSD= 01/01/2023, SED= 31/12/2023, Value (ARR) = 5,000€.

I want to show the sum of the active opportunities in a table that has the end of months in columns. 

In the previous example, Client 1 is Active between SSD and SED, therefore I should see 5,000€ in every column between Jan 23 and Dec 23.

I also have a Calendar table with Dates and a end of month column calculated from dates.

my first measure is

Total ARR = SUM('Unpivoted DB Ungrouped'[Value])

the measure I'm showing in the matrix is the following:

Monthly ARR = CALCULATE(
[Total ARR],
FILTER(
'Flat DB Ungrouped',
'Flat DB Ungrouped'[Service Start Date] <= ENDOFMONTH('Calendar'[Date]) &&
'Flat DB Ungrouped'[Service End Date] >= ENDOFMONTH('Calendar'[Date])
)
)

 Here is the relationship with the Calendar

and here is how I set-up the matrix:

I'm not sure about the monthly-ARR Dax code, the goal is to use the end-of-month filters in the calcultate filter.

What am I doing wrong, I'm clearly not getting the right results:

in the previous image, the 4,000 belongs to an opportunity going between the 8/8/2023 and the 7/08/2024.

 Thanks in advance for your help!

[EliasAR]

@DataNinja777 Thanks a lot for your help. That was the problem, now that you mentionned it, I can clearly see how it was affecting the results.

Cheers!

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[DataNinja777 · Accepted solution]

Hi @EliasAR 

In order for your duration calculation (yellow highlighted part) to perform in the manner you intended, you need to have the calendar table and your fact tables as disconnected tables.  This is because you have start date and end date in your fact table (two date fields) and because of multiple date fields, you should not create relationship with the calendar table for it to calculate the duration properly.  

The technique used is similar to headcount formula, albeit amounts are involved in your service revenue projection.  

The link below shows an example of the use of disconnected tables to achive the similar result which you require relating to the duration calculation.  

https://p3adaptive.com/finding-the-magic-part-2-on-the-way-to-data-happiness/

Best regards,