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 enterprise Date ini End date Monthl...
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:

 

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

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

      This depends on how they are related.

       

      What is the active relationship?