Total sum of showed values

[ecoflux38170]

Hello everyone,

I created a measure allowing to subtract indexes between 2 dates, but the total displayed is not the correct one, and I would like to have the total of the values ​​displayed in my matrix.

Here are some screenshots to show you how it works.

Table Index kilométrique : 

Measure 1 : Index kilométrique(km) = SUM('Index Kilométrique'[Index kilométrique])

Measure 2: 

Kilométrage =

IF(

    ISFILTERED('Chronologie'[Chronologie]),

    ERROR("Les mesures rapides de Time Intelligence peuvent être regroupées ou filtrées seulement par la hiérarchie de dates ou les colonnes de dates principales fournies par Power BI."),

    VAR __PREV_YEAR = CALCULATE([Index kilométrique(km)], DATEADD('Chronologie'[Chronologie].[Date], -1, YEAR))

    VAR __CURRENT_YEAR = ('Index Kilométrique'[Index kilométrique(km)])

    VAR __CALCUL = (__CURRENT_YEAR - __PREV_YEAR)

    VAR __RESULTAT = IF(__CALCUL < 0, BLANK(), __CALCUL)

    RETURN

        __RESULTAT

)

Matrice :

I would like finally to make a graph with annual kms for my fleet.

Thanks for your help !

[tamerj1 · Accepted solution]

Hi @ecoflux38170 

Please try

Kilométrage =
IF (
    ISFILTERED ( 'Chronologie'[Chronologie] ),
    ERROR ( "Les mesures rapides de Time Intelligence peuvent être regroupées ou filtrées seulement par la hiérarchie de dates ou les colonnes de dates principales fournies par Power BI." ),
    SUMX (
        VALUES ( 'Table'[Libellé] ),
        VAR __PREV_YEAR =
            CALCULATE (
                [Index kilométrique(km)],
                DATEADD ( 'Chronologie'[Chronologie].[Date], -1, YEAR )
            )
        VAR __CURRENT_YEAR = [Index kilométrique(km)]
        VAR __CALCUL = __CURRENT_YEAR - __PREV_YEAR
        VAR __RESULTAT =
            IF ( __CALCUL < 0, BLANK (), __CALCUL )
        RETURN
            __RESULTAT
    )
)

[tamerj1 · Accepted solution]

Hi @ecoflux38170 

Please try

Kilométrage =
IF (
    ISFILTERED ( 'Chronologie'[Chronologie] ),
    ERROR ( "Les mesures rapides de Time Intelligence peuvent être regroupées ou filtrées seulement par la hiérarchie de dates ou les colonnes de dates principales fournies par Power BI." ),
    SUMX (
        VALUES ( 'Table'[Libellé] ),
        VAR __PREV_YEAR =
            CALCULATE (
                [Index kilométrique(km)],
                DATEADD ( 'Chronologie'[Chronologie].[Date], -1, YEAR )
            )
        VAR __CURRENT_YEAR = [Index kilométrique(km)]
        VAR __CALCUL = __CURRENT_YEAR - __PREV_YEAR
        VAR __RESULTAT =
            IF ( __CALCUL < 0, BLANK (), __CALCUL )
        RETURN
            __RESULTAT
    )
)

[ecoflux38170]

@tamerj1 I've another question for you.

The measurement is correct, but the years are off (see image below)

Here are the readings used:

This means that :
For a statement made in 2017, the mileage corresponds to 2016; however, on the matrix, it is displayed in 2017.

Is there a solution to work around this problem?

Thanks for your help !

[tamerj1 · Accepted solution]

Hi @ecoflux38170 
Not Sure if I fully understand. However, please try

Kilométrage =
IF (
    ISFILTERED ( 'Chronologie'[Chronologie] ),
    ERROR ( "Les mesures rapides de Time Intelligence peuvent être regroupées ou filtrées seulement par la hiérarchie de dates ou les colonnes de dates principales fournies par Power BI." ),
    SUMX (
        VALUES ( 'Table'[Libellé] ),
        VAR __NEXT_YEAR =
            CALCULATE (
                [Index kilométrique(km)],
                DATEADD ( 'Chronologie'[Chronologie].[Date], 1, YEAR )
            )
        VAR __CURRENT_YEAR = [Index kilométrique(km)]
        VAR __CALCUL = __NEXT_YEAR - __CURRENT_YEAR
        VAR __RESULTAT =
            IF ( __CALCUL < 0, BLANK (), __CALCUL )
        RETURN
            __RESULTAT
    )
)

[ecoflux38170]

@tamerj1 I want exactly this ! Thank you so much !

[ecoflux38170]

@tamerj1 thanks a lot, it works !