Creating Evolution %

[RobbeVL]

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 column to Values, but how can I code this?)

Below you'll see what I mean visually, under that the current matrix

[v-ljerr-msft]

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. 

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]

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]

Hi @RobbeVL,

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

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]

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]

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.

Regards

[RobbeVL]

Hi there @v-ljerr-msft

Thanks for the swift replies again!

In the dropbox link below you'll find a masked file. (tab "Key Account Overview")

https://www.dropbox.com/s/1ug0ocgdg9lvy63/DatamodelParcify_CSV_RobbeUpload.pbix?dl=0 

Please have a look! 🙂

Regards,

[v-ljerr-msft · Accepted solution]

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. 

Regards

[RobbeVL]

Awsome! Thank you !

[RobbeVL]

No one ? 😞