Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

Dax problem

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

enterpriseDate iniEnd dateMonthly Value
A01/01/202231/12/20244
A01/01/202131/12/20233
A01/04/202030/06/20202
B01/01/202031/03/20206
B01/01/202131/03/20215

And I need to get this:

YEARSum value of the year depending on the number of months
202024
202151
202284
202384
202448

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

Thanks in advance

7 Replies

  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    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's avatar
      Syndicate_Admin
      Administrator
      3 years ago

      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's avatar
        MFelix
        Super User
        3 years ago

        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: