Forum Discussion

RobbeVL's avatar
RobbeVL
Impactful 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
Microsoft 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

  • RobbeVL's avatar
    RobbeVL
    Impactful Individual
    9 years ago

    Thank you very much for your reply v-ljerr-msft .

    "# Parcels" is actually already a measure, how can I adjust the formula to that ?

     

    • v-ljerr-msft's avatar
      v-ljerr-msft
      Microsoft Employee
      9 years ago

      Hi RobbeVL,

       

      So could you try the formula below to see if it works? :smileyhappy:

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

       

      Regards

      • RobbeVL's avatar
        RobbeVL
        Impactful Individual
        9 years ago
        Evolution/Week = 
VAR currentWeek =
    MAX ( 'DimDate'[Weeknumber] )
VAR currentBrand =
    FIRSTNONBLANK ( 'General_Deliveries'[BRAND] ; 2)
RETURN
    DIVIDE (
        [#Parcels]
            - CALCULATE (
                [#Parcels] ;
                FILTER (
                    ALL ( 'DimDate' );
                    'Dimdate'[Weeknumber]
                        = currentWeek - 1
                        && 'General_Deliveries'[BRAND] = currentBrand
                )
            );
        CALCULATE (
            [#Parcels];
            FILTER (
                ALL ( 'Dimdate' );
                'Dimdate'[Weeknumber]
                    = currentWeek - 1
                    && 'General_Deliveries'[BRAND] = currentBrand
            )
        )
    )

        v-ljerr-msft
        Using code above, I still get an error 
        "A single number for column BRAND cannot be determined.."