CROSSJOIN not giving required results - too many duplicates

[sabilahmed]

Hello,

I have created a new table via a CROSSJOIN between a Agreement Additions table and a Dim_Calendar table. See below:

I have connected them using the StartofMonth column from Agreement Additions and Date column from Dim_Calendar (relationship model).

Below is the DAX for the new table 'Contracted':

Contracted = 
CROSSJOIN (
    'AgreementAdditions & List',
        SUMMARIZE (
            FILTER (
                'Dim_Calendar',
                'Dim_Calendar'[Date] > MIN ( 'AgreementAdditions & List'[START_DATE] )
                    && 'Dim_Calendar'[Date] < MAX ( 'AgreementAdditions & List'[END_DATE] )
            ),
            Dim_Calendar[StartofMonth]
        )
    )

Below are some example columns from the Agreement Additions table where the key focus is:

As you can see the above table has a start and end date per agreement and a monthly billing amount. My goal is to repeat the number of rows per month between the start and end date. However, the resulting 'Contracted' table is not correct. See below why this is:

The above table is what is formed from the CROSSJOIN (between Agreement Additons and Dim_Calendar). The above example agreement has a start date from Aug 2022 - Aug 2023, therefore I would expect 12 rows (1 row per month) of £9.40 for this example, but instead I get 56 rows which is strange.

When I plot a matrix viz, this is what I get:

What i'm expecting to see is the below results:

What am I doing wrong?

[amitchandak · Accepted solution]

@sabilahmed , There are three approaches I have discussed do deal with date and start and end date, check if one of them can help

https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

Measure way

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...