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

RobbeVL's avatar
RobbeVL
Icon for Impactful Individual rankImpactful 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
Icon for Microsoft Employee rankMicrosoft 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
    Icon for Impactful Individual rankImpactful 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.."

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

      Hi RobbeVL,

       

      The formula seems to be fine.

       

      Could you share a sample pbix file which can reproduce the issue, so that we can help further investigate on it? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.:smileyhappy:

       

      Regards