proyectado por mes

[pablol1235]

Amigos: a ver si esto se puede hacer en PBI

La primer fila es el año 2023 y la segunda 2024.

Les dejo la fórmula que uso en exceL

Para calcular el mes proyectado, hace:

El promedio de compra de los últimos 6 meses y le considera el 50% del promedio de variación de los últimos 2 años de dicho mes (respecto de sus correspondientes 6 meses anteriores).

Gracias

[pablol1235]

Por las dudas que no puedas abrir el enlace de github que envie

[v-linyulu-msft · Accepted solution]

Hi, @pablol1235 
Thank you for your prompt response. I now fully understand your requirements.

I apologize for the delay. I realize that your needs involve very complex recursion, specifically the SUM(P43:U43) part. Recursion cannot be implemented in DAX. While this is easily achievable in Excel, it is not possible in DAX. Recursive calculations require row-by-row data processing, which is incompatible with DAX's batch processing mode. Here is a screenshot from the relevant documentation:

For further details, please refer to:

Solved: Power BI Recursion - DAX - Microsoft Fabric Community

Solved: Recursion in DAX - Microsoft Fabric Community

Previous Value (“Recursion”) in DAX – Greg Deckler

Currently, I have managed to implement all parts except for the SUM(P43:U43) portion based on the calculated table. However, the recursion, represented by my variables P1 and F1, cannot fulfill your requirements.

Table 2 = 
    VAR t1 = SUMMARIZE(
        'Table',
        'Table'[month],
        'Table'[Mes],
        'Table'[blank]
    )
    VAR t2 = SUMMARIZE(
        'Table',
        'Table'[month],
        "Mes", VAR CM = 'Table'[month]
        VAR v40 = CALCULATE(
            SUM('Table'[Mes]),
            FILTER(
                ALL('Table'),
                'Table'[month] = CM && 'Table'[blank] = "Compras AA"
            )
        )
        VAR D1 = CALCULATE(
            SUM('Table'[Mes]),
            FILTER(
                ALL('Table'),
                'Table'[month] < CM && 'Table'[month] >= EDATE(
                    CM,
                    -7
                ) && 'Table'[blank] = "Compras AA"
            )
        )
        VAR SECOND = (v40 / D1 - 1) * 0.5 + 1
        VAR LM = EDATE(
            'Table'[month],
            -12
        )
        VAR J40 = CALCULATE(
            SUM('Table'[Mes]),
            FILTER(
                ALL('Table'),
                'Table'[month] = LM && 'Table'[blank] = "Compras AA"
            )
        )
        VAR D2 = CALCULATE(
            SUM('Table'[Mes]),
            FILTER(
                ALL('Table'),
                'Table'[month] < LM && 'Table'[month] >= EDATE(
                    LM,
                    -7
                ) && 'Table'[blank] = "Compras AA"
            )
        )
        VAR THRID = (J40 / D2 - 1) * 0.5 + 1
        VAR F = DIVIDE(
            (SECOND + THRID),
            2
        )
        VAR P1 = CALCULATE(
            SUM('Table'[Mes]),
            FILTER(
                ALL('Table'),
                'Table'[month] < CM && 'Table'[month] >= EDATE(
                    CM,
                    -7
                ) && 'Table'[blank] = "Compras A. Vig."
            )
        )
        VAR F1 = DIVIDE(
            P1,
            6
        ) * F
        RETURN
            IF(
                ISERROR(F1),
                BLANK(),
                F1
            ),
        "blank", "Compra Proyectada"
    )
    VAR F2 = UNION(
        t1,
        t2
    )
    RETURN
        F2

Please find the attached pbix relevant to the case.

 
Thank you for your effort and understanding.Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
 

[pablol1235]

Muchas gracias por la repuesta

Entiendo lo que me dices y entiendo por qué no se puede

Con respecto al resto que me envías me funcionó muy bien

Muchas gracias por el esfuerzo y la paciencia

Slds

[pablol1235]

Release proyectado por mes · pablolem763/proyectado-por-mes · GitHub (en inglés)

TE ENVÍO

[pablol1235]

Hola

Ahora si se ve bien?

[v-linyulu-msft]

Hi, @pablol1235 
 

Thank you for your prompt response.

The images you provided have given me a better understanding of your needs, especially regarding the Excel formulas. However, I must inform you that I am still unable to open the provided link, despite multiple attempts.

Additionally, in your screenshots, I cannot clearly identify which columns correspond to the referenced cells. I need a screenshot of the following part in Excel:

 As you know, I can understand your logic through your screenshots, but I do not know the data corresponding to the referenced cells.

To better address this issue, I suggest you try sharing the Excel file with us via GitHub. This method is more likely to be successful. I look forward to your response.

Thank you again for your cooperation and understanding.

Best Regards,

Leroy Lu

[pablol1235]

Subo imágenes por las dudas

[v-linyulu-msft]

Hi, @pablol1235 
Thanks for reaching out to the Microsoft fabric community forum.

Could you please share your Excel file with us? Due to translation issues, we do not fully understand the information in the images, making it difficult to comprehend your Excel formula completely.

Additionally, is your Excel formula applied in column AC or in row 4? Please ensure to remove any sensitive information.

We highly value your issue and aim to help you find a solution as soon as possible. Sharing your file will help us better understand and analyze the problem, allowing us to provide more accurate assistance.

For questions about uploading data, you can try the following links:

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Solved: How to upload PBI in Community - Microsoft Fabric Community

Thank you for your understanding and cooperation. If you have any other questions or need further assistance, please feel free to let us know.

Best Regards,

Leroy Lu

[pablol1235]

Amigos

Les comparto un enlace al excel

La fórmula que no puedo hacer se encuentra en la celda V43

Espero puedan ayudarme

Gracias

https://docs.google.com/spreadsheets/d/18lomS5CZMnu2Maa_uiJiwogq2wrVTZyX/edit?usp=sharing&ouid=10151...

[v-linyulu-msft]

Hi, @pablol1235 

Thank you for your prompt response.

However, I am currently unable to open your link. Could you please try other methods, such as SharePoint or GitHub?When uploading a file, please be careful to delete sensitive information.

If you have any other questions please feel free to contact me.

Best Regards,

Leroy Lu

[pablol1235]

No hay forma de adjuntar un Excel? O un correo ¿dónde enviarlo?

No tengo las otras opciones que me pasas

Te dejo un enlace a Dropbox

https://www.dropbox.com/scl/fi/7zdwc0h0woez4kwqxrk6a/Libro5.xlsx?rlkey=9wxf9gx24pmbhu41yc6w6xr60&st=...