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

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?

    • MFelix's avatar
      MFelix
      Super User
      3 years ago

      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.