Forum Discussion

FlorianF28's avatar
FlorianF28
New Member
1 year ago
Solved

Problème Formule DAX

Bonjour à vous, Je me permets de poster ce message car j'ai un problème avec des formules Power BI, je souhaite analyser un fichier d'écritures comptables. Malheureusement en 2022 il existait des co...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi FlorianF28 ,

     

    I think you can try this code to create the measure.

    Solde_N_Moins_1 =
VAR xDate =
    YEAR (
        MAXX (
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Libellé] = MAX ( 'Table'[Libellé] )
                    && 'Table'[Compte-Lib] = MAX ( 'Table'[Compte-Lib] )
            ),
            'Table'[Date]
        )
    ) - 1
RETURN
    CALCULATE (
        [Solde],
        ALL ( F_Ecritures[FISCAL_YEAR] ),
        F_Ecritures[FISCAL_YEAR] = xDate
    )
    Measure = SUMX(SUMMARIZE('Table','Table'[Libellé],'Table'[Compte-Lib]),[Solde_N_Moins_1])

    My Sample:

    F_Ecritures = ADDCOLUMNS(CALENDARAUTO(),"FISCAL_YEAR",YEAR([Date]))

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

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

     

     

     

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
1 year ago

Hi FlorianF28 - The issue occurs because the dynamic variable year isn’t capturing the context of accounts that existed in 2022 but not in 2023.

 

Modified formaule:

Solde_N_Moins_1 =
VAR xDate =
IF(
MAX(F_Ecritures[FISCAL_YEAR]) = 2023, 2022,
IF(MAX(F_Ecritures[FISCAL_YEAR]) = 2022, 2021)
)
RETURN
CALCULATE(
[Solde],
ALL(F_Ecritures[FISCAL_YEAR]), // Clears the filter on FISCAL_YEAR to get all accounts
F_Ecritures[FISCAL_YEAR] = xDate
)

 

I hope this works as expected. Please check