Issue with Measure for Monthly cumulative calculation

[jason_beck14]

Hello,

I have an issue displaying a measure as a monthly cumulative value relative to two date values. 

The data I'm working with is population values within a start and end date and by country:

Organisation Name	final_org_id	Start_Date	Contract_End	contract_id	country_name	population
ABC Org	384003255	1/11/2022	31/10/2023	384005431	MYANMAR	5
ABC Org	384003255	1/02/2023	31/10/2023	384005431	MYANMAR	5
DEF Org	384003328	1/08/2022	31/07/2023	384003928	MYANMAR	17
GHI Org	384003330	1/07/2022	30/06/2023	384004749	MYANMAR	4
JKL Org	384008483	1/09/2022	31/08/2023	384005132	MYANMAR	1
MNO Org	384009322	1/08/2022	31/07/2023	384005194	MYANMAR	3
PQR Org	384009613	1/01/2023	31/01/2024	384005926	MYANMAR	28

And in some cases a single Org will have several changes in population within a single contract period:

Organisation Name	final_org_id	Start_Date	Contract_End	contract_id	country_name	population
123 Org	384007913	1/05/2022	30/04/2023	384004452	SINGAPORE	1853
123 Org	384007913	1/07/2022	30/04/2023	384004452	SINGAPORE	2730
123 Org	384007913	1/10/2022	30/04/2023	384004452	SINGAPORE	3119
123 Org	384007913	1/01/2023	30/04/2023	384004452	SINGAPORE	3355
123 Org	384007913	1/04/2023	30/04/2023	384004452	SINGAPORE	3850

This is my measure:

Monthly Pop Total =

VAR __month =

    STARTOFMONTH ( 'Calendar Reference'[Date].[Date] )

RETURN

    CALCULATE (

        [Total Population],

        FILTER (

            ORG_Pop_Data,

            MAX ( ORG_Pop_Data[Effective_Date] ) <= __month

                && ORG_Pop_Data[Contract_End].[Date] >= __month

        ),

        ORG_Pop_Data[population]

    )

And data model:

Current output:

I need to display the total population monthly by country.

But how I want it to sum the population values is it should sum all Population by country where the month is >= Start_Date and <= Contract_End like below using the Myanmar data in the table above:

	2022						2023
	July	August	September	October	November	December	January	February	March
Myanmar	4	24	25	25	30	30	58	63	63

[v-shex-msft · Accepted solution]

HI @jason_beck14,

I'm not so clear how the ‘Total Population’ expression calculated, can you please share some more detail about these?

How to Get Your Question Answered Quickly  

In addition, you can also take a look to the Greg’s blog about analysis on start, end date range define by multiple date fields if hleps:

Before You Post, Read This: start, end date  
Regards,

Xiaoxin Sheng