Forum Discussion

mavn23061997's avatar
mavn23061997
Frequent Visitor
3 years ago
Solved

Dax problem

Estimados buenas tardes quiero solicitarles su ayuda para poder realizar la siguiente medida o sugenerencia para lograr lo siguiente  tengo los siguientes datos empresa fecha ini fecha fin va...
Need Help
Tips and Tricks
  • Syndicate_Admin's avatar
    Syndicate_Admin
    3 years ago

    @mavn23061997 ,

    Supongo que la tabla de años está separada de la otra tabla, cree la siguiente medida:

    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] )

    Ahora solo use esta medida con la columna de años:

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

@mavn23061997 ,

Supongo que la tabla de años está separada de la otra tabla, cree la siguiente medida:

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] )

Ahora solo use esta medida con la columna de años:

mavn23061997's avatar
mavn23061997
Frequent Visitor
3 years ago

Lo acabo de Probar y funciona, solo por curiosidad si en caso tuviese tabla calendario relacionadas con fecha de inicio y fecha fin , como se podria lograr el mismo resultado

 

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    3 years ago

    Esto depende de cómo se relacionen.

    ¿Cuál es la relación activa?

    • mavn23061997's avatar
      mavn23061997
      Frequent Visitor
      3 years ago

      Fecha Inicio

      • Syndicate_Admin's avatar
        Syndicate_Admin
        Administrator
        3 years ago

        Pruebe la siguiente fórmula:

        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] )

        No olvides aceptar la respuesta correcta para ayudar a los demás.