Dax problem

[Syndicate_Admin]

Dear good afternoon I want to ask for your help to be able to perform the following measure or generacy to achieve the following

I have the following data

enterprise	Date ini	End date	Monthly Value
A	01/01/2022	31/12/2024	4
A	01/01/2021	31/12/2023	3
A	01/04/2020	30/06/2020	2
B	01/01/2020	31/03/2020	6
B	01/01/2021	31/03/2021	5

And I need to get this:

YEAR	Sum value of the year depending on the number of months
2020	24
2021	51
2022	84
2023	84
2024	48

where you have to add depending on the year and number of months that represents in that year the monthly value

Thanks in advance

[MFelix]

Hi @manv23061997

Can you explain a little biut better what you want to achieve? Not understanding what means the  Sum value of the year depending on the number of months.

How do you get 24 for 2020 and 51 for 2021.

[Syndicate_Admin]

If I tell you

Result 24 for 2020 is obtained as follows:

In the box I can see that there is a record of the company "A" that has as its start date: 01/04/2020 and final file: 30/06/2020 between that range of dates there are 3 months of difference, therefore 3 months multiplied by the monthly value 2 gives me 6 as a result in the year 2020, then there is also a record "B" that has as a start date: 01/01/2020 and end date: 31/03/2020 Between those dates there is 3 months of difference multiplied by the monthly value 6 results in 18.

So in the year 2020 I have as a total value 6 + 18 = 24

Result 51 for the year 2021 is obtained in the same way only considering the total value of the records that have included the year within the period of start date and end date.

[MFelix]

Hi @mavn23061997 ,

I assume the years table is separated from the other table, create the following measure:

Values per Month = 
VAR temptable =
    ADDCOLUMNS (
        FILTER (
            ADDCOLUMNS (
                CROSSJOIN (
                    'Years_Table',
                    ADDCOLUMNS (
                        'DataValues',
                        "StartYear", YEAR ( 'DataValues'[Date ini] ),
                        "EndYear", YEAR ( 'DataValues'[End date] )
                    )
                ),
                "Flag",
                    IF ( [StartYear] <= [Years] && [EndYear] >= [Years], 1, 0 )
            ),
            [Flag] = 1
        ),
        "TotalMonths",
            SWITCH (
                TRUE (),
                [StartYear] = [Years],
                    DATEDIFF (
                        'DataValues'[Date ini],
                        MIN ( DATE ( [years], 12, 31 ), 'DataValues'[End date] ),
                        MONTH
                    ) + 1,
                [StartYear] > [Years], DATEDIFF ( DATE ( YEAR ( [StartYear] ), 12, 31 ) + 1, 'DataValues'[Date ini], MONTH ),
                [StartYear] < [Years]
                    && [EndYear] > [Years], DATEDIFF ( DATE ( [Years], 1, 1 ), DATE ( [Years], 12, 31 ), MONTH ) + 1,
                [EndYear] = [Years], DATEDIFF ( DATE ( [years], 1, 1 ), 'DataValues'[End date], MONTH ) + 1
            )
    )
RETURN
    SUMX ( tempTable, [TotalMonths] * 'DataValues'[Monthly Value] )

Now just use this measure with the years column:

[Syndicate_Admin]

I just tried it and it works, just out of curiosity if in case I had a calendar table related to start date and end date, how could the same result be achieved

[MFelix]

This depends on how they are related.

What is the active relationship?

[Syndicate_Admin]

Start date

[MFelix]

Try the following formula:

Values per Month = 
VAR temptable =
    ADDCOLUMNS (
        FILTER (
            ADDCOLUMNS (
                CROSSJOIN (
                    DISTINCT('Calendar'[Year]),
                    ADDCOLUMNS (
                        'DataValues',
                        "StartYear", YEAR ( 'DataValues'[Date ini] ),
                        "EndYear", YEAR ( 'DataValues'[End date] )
                    )
                ),
                "Flag",
                    IF ( [StartYear] <= [Year] && [EndYear] >= [Year], 1, 0 )
            ),
            [Flag] = 1
        ),
        "TotalMonths",
            SWITCH (
                TRUE (),
                [StartYear] = [Year],
                    DATEDIFF (
                        'DataValues'[Date ini],
                        MIN ( DATE ( [year], 12, 31 ), 'DataValues'[End date] ),
                        MONTH
                    ) + 1,
                [StartYear] > [Year], DATEDIFF ( DATE ( YEAR ( [StartYear] ), 12, 31 ) + 1, 'DataValues'[Date ini], MONTH ),
                [StartYear] < [Year]
                    && [EndYear] > [Year], DATEDIFF ( DATE ( [Year], 1, 1 ), DATE ( [Year], 12, 31 ), MONTH ) + 1,
                [EndYear] = [Year], DATEDIFF ( DATE ( [year], 1, 1 ), 'DataValues'[End date], MONTH ) + 1
            )
    )
RETURN
    SUMX ( tempTable, [TotalMonths] * 'DataValues'[Monthly Value] )

Don't forget to accept the correct answer to help others.