Forum Discussion

RobbeVL's avatar
RobbeVL
Icon for Impactful Individual rankImpactful Individual
9 years ago
Solved

Creating Evolution %

Hi all,   I created a matrix with some data of the amount of deliveries a partner receives per week. I'd like to add the evolution per week to the matrix, how can I do this? (visually i'd add a co...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi RobbeVL,

     

    Based on my test, the formula below should work in your scenario. 

    Evolution/Week =
VAR currentYear =
    MAX ( DimDate[CalendarYear] )
VAR currentWeek =
    MAX ( 'DimDate'[Weeknumber] )
RETURN
    DIVIDE (
        [#Parcels]
            - CALCULATE (
                [#Parcels],
                FILTER (
                    ALLEXCEPT ( General_Deliveries, General_Deliveries[BRAND] ),
                    RELATED ( 'DimDate'[CalendarYear] ) = currentYear
                        && RELATED ( 'Dimdate'[Weeknumber] )
                            = currentWeek - 1
                )
            ),
        CALCULATE (
            [#Parcels],
            FILTER (
                ALLEXCEPT ( General_Deliveries, General_Deliveries[BRAND] ),
                RELATED ( 'DimDate'[CalendarYear] ) = currentYear
                    && RELATED ( 'Dimdate'[Weeknumber] )
                        = currentWeek - 1
            )
        )
    )

     

    Here is the modified pbix file for your reference. :smileyhappy:

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi RobbeVL,

 

According to your description above, you should be able to use the formula below to create a new measure to calculate the EVO, then show it on the Matrix in your scenario. :smileyhappy:

measure =
VAR currentWeek =
    MAX ( 'Table1'[Weeknumber] )
VAR currentBrand =
    FIRSTNONBLANK ( 'Table1'[BRAND], 1 )
RETURN
    DIVIDE (
        SUM ( 'Table1'[#Parcels] )
            - CALCULATE (
                SUM ( 'Table1'[#Parcels] ),
                FILTER (
                    ALL ( 'Table1' ),
                    'Table1'[Weeknumber]
                        = currentWeek - 1
                        && 'Table1'[BRAND] = currentBrand
                )
            ),
        CALCULATE (
            SUM ( 'Table1'[#Parcels] ),
            FILTER (
                ALL ( 'Table1' ),
                'Table1'[Weeknumber]
                    = currentWeek - 1
                    && 'Table1'[BRAND] = currentBrand
            )
        )
    )

Note: replace 'Table1' with your real table name.

 

Regards