Necesita ayuda urgente - Cálculo DAX

[Kolumam]

Tengo la tabla de abajo.

Fecha de inicio del contrato	Fecha de finalización del contrato	Nombre de la operación	Precio completo de O&M
1/7/2019	31/8/2019	X	375
1/9/2019	31/3/2020	X	405
12/5/2020	31/12/2021	X	385
1/4/2017	31/3/2018	Y	380
1/4/2018	31/3/2020	Y	410
11/5/2020	31/12/2021	Y	303

En función de la fecha de inicio del contrato y la fecha de finalización del contrato para cada nombre de operación, necesito calcular el contrato anual para cada nombre de operación.

Salida esperada con cálculo:

Nombre de la operación	año	Contrato Anual	Cálculo
X	2019	395	(375*60/180)+(405*120/180)
X	2020	353.01	(405*90/365)+(385*240/365)
X	2021	385	(385*365/365)
Y	2017	380	(380*365/365)
Y	2018	396.97	(380*90/365)+(410*270/365)
Y	2019	410	(410*365/365)
Y	2020	300.23	(410*90/365)*(303*240/365)
Y	2021	303	303*365/365

Gracias de antemano por la ayuda.

@amitchandak @parry2k @mahoney19 @Amit @amitchandak @parry2k @az38 @jdbuchanan71 @mahoneypat @edhans @harshnathani @v-kellya-msft @MFelix @Ashish_Mathur @BA_Pete @ryan_mayu @kbuckvol @Alexander76877 @Petazo @Mariusz @TomMartens @Greg_Deckler @tjd @Sean @mikstra @AllisonKennedy @EricHulshof @briandpeterson @USG_Phil @vpatel55 @mwegener @v-piga-msft @tex628 @sturlaws @Vvelarde @CheenuSing @MarcelBeug @Zubair_Muhammad @v-piga-msft @danextian @MattAL @MattAllington @roalexan @Alexander76877 @kgc

[Greg_Deckler]

@Kolumam - ¿Puede explicar la lógica detrás de los cálculos que ha presentado?

Así, por ejemplo,

(375*60/180)+(405*120/180)

Entonces, entiendo de dónde vienen los 375 y 405. ¿Es el 60 y 120 el número de meses dentro de 2019 para los que el contrato es válido y usted está asumiendo 30 días meses? ¿De dónde viene el 180? Yo pensaría 60 + 120 pero luego la siguiente línea es:

(405*90/365)+(385*240/365)

Y 90 y 240 no suman 365.

Entonces usted tiene el 2021 cosas donde de repente se cambia a 365/365 (que es 1) así que.... Desconcertado.

[Kolumam]

Hola @Greg_Deckler

Por favor, encuentre mi explicación para el cálculo.

(375*60/180)+(405*120/180)

Aquí 60 es el número de días para los que el contrato es válido (fecha de finalización - fecha de inicio) y 180 es el número total de días entre el 1 de julio de 2019 y el 31 de diciembre de 2019. Estoy usando los días aproximadamente, pero idealmente debe ser el número exacto de días.

Para este: (405*90/365)+(385*240/365)

90 porque el contrato es del 1 de enero de 2020 al 31 de marzo de 2020 y 240 se debe a que la fecha de inicio es 12/5/2020 y finaliza el 31 de diciembre de 2020. Estoy tomando números aproximados. No es la diferencia exacta en los días.

Para el último contrato, el contrato se aplica para todo el año. Por lo tanto 385*365/365

[Mariusz]

Hola @Kolumam

si está buscando dividir la tasa proporcionalmente por días, considere las soluciones adjuntas mediante Power Query y la tabla DAX

Saludos
Mariusz

Si este post ayuda, entonces considere aceptarlo como la solución.

Por favor, siéntase libre de conectarse conmigo.
Linkedin

[Kolumam]

Hola @Mariusz

Gracias por intentarlo, pero su solución es incorrecta. Vea a continuación.

Para 2019, usted está tomando todo el año, más bien sólo debe tomar el número de días entre el 1 de julio de 2019 y el 31 de diciembre de 2019. Así será (375*60/180)+(405*120/180) que es 395. Como puede ver para el contrato X, tenemos dos contratos en el mismo año. Si eso sucede, entonces usted necesita dividir por el número de días desde la fecha de inicio del contrato hasta el final del año de la fecha de inicio del contrato. Si no hay dos contratos en un año, por ejemplo, la fecha de inicio es el 1 de abril de 2017 para el contrato Y, en ese caso para 2017, debería tomar todo el año. Así que será (380 * 365 * 365) que es 380 en lugar de 286,83. A partir de 2018, el valor se prorratea en consecuencia.

¿Entiendes?

[Kolumam]

Hola gente,

Mi pregunta aún no está resuelta. ¿Alguien puede ayudar?

@parry2k @mahoney19 @Amit @parry2k @az38 @jdbuchanan71 @mahoneypat @edhans @harshnathani @v-kellya-msft @MFelix @Ashish_Mathur @BA_Pete @ryan_mayu @kbuckvol @Alexander76877 @Petazo @Mariusz @TomMartens @Greg_Deckler @tjd @Sean @mikstra @AllisonKennedy @EricHulshof @briandpeterson @USG_Phil @vpatel55 @mwegener @v-piga-msft @tex628 @sturlaws @Vvelarde @CheenuSing @MarcelBeug @Zubair_Muhammad @v-piga-msft @danextian @MattAL @MattAllington @roalexan @Alexander76877 @kgc

[ImkeF]

Hola @Kolumam ,

como los otros ya han mencionado, el problema con el ejemplo que ha proporcionado es que hay incoherencias / errores en él que hacen que determinar la lógica deseada difícil.

El código siguiente calcula los valores proporcionales según el número real de días del año:

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "dc1LCsAwCATQu7gOqPkQc5OW4P2vUQ2RhkJ3M/IY5wTGjpl4QILCKJEv772BJifjIMVypk0qbZKxxd2MVSscOxI71Xf6u8Ni+V6ETiKfV04qb8I/r9YOFVB9AA==", 
                    BinaryEncoding.Base64
                ), 
                Compression.Deflate
            )
        ), 
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table[
                #"Start Date of Contract" = _t, 
                #"End Date of Contract" = _t, 
                #"Operation Name" = _t, 
                #"Comprehensive O&M Price" = _t
            ]
    ),
    #"Changed Type" = Table.TransformColumnTypes(
        Source, 
        {
            {"Start Date of Contract", type date}, 
            {"End Date of Contract", type date}, 
            {"Operation Name", type text}, 
            {"Comprehensive O&M Price", Int64.Type}
        }
    ),
    AddListOfYears = Table.AddColumn(
        #"Changed Type", 
        "Year", 
        each {Date.Year([Start Date of Contract])..Date.Year([End Date of Contract])}
    ),
    #"Expanded ListOfYears" = Table.ExpandListColumn(AddListOfYears, "Year"),
    AddStart = Table.AddColumn(
        #"Expanded ListOfYears", 
        "Start", 
        each List.Max({[Start Date of Contract], #date([Year], 1, 1)})
    ),
    AddEnd = Table.AddColumn(
        AddStart, 
        "End", 
        each List.Min({[End Date of Contract], #date([Year], 12, 31)})
    ),
    AddDuration = Table.AddColumn(
        AddEnd, 
        "DurationInDays", 
        each Duration.Days([End] - [Start]) + 1, 
        Int64.Type
    ),
    AddDaysInYear = Table.AddColumn(
        AddDuration, 
        "DaysInYear", 
        each if Date.IsLeapYear(#date([Year], 1, 1)) then 366 else 365
    ),
    AddAnnualShare = Table.AddColumn(
        AddDaysInYear, 
        "AnnualShare", 
        each [DurationInDays] / [DaysInYear]
    ),
    WeightPriceByShare = Table.AddColumn(
        AddAnnualShare, 
        "AnnualContractValue", 
        each [#"Comprehensive O&M Price"] * [AnnualShare], 
        type number
    )
in
    WeightPriceByShare

Crea una tabla en el editor de consultas que con suerte le permitirá seguir la lógica y ver dónde sus números son incorrectos:

En general, sus números son demasiado bajos porque su uso 365 días al año y ponderar sus meses sólo con 30 días (sumando hasta 360).

Una incoherencia específica en su cálculo es que asigna años finales parciales pro rata (es decir, Y 410 para 2020: 90 días (enero-mar)) pero todos los años de inicio parciales con año completo (380 Y con 365 días, aunque el contrato comienza en abril y X 395 dividido por solo 180 (en lugar de 360)).
Por lo tanto, mi cálculo supone que el "Precio completo de O&M" es una cuota anual que debe asignarse a años parciales de manera consistente.