Compare monthly sales with average sales of last 90 days from that month

[Atinder]

Hello all, 

I want to create a report that shows sales growth up and down for each item compare with average of last 90 days or 3 months from that month. show me growth rate by %. 

Below is the example I want to achieve in the powerbi report. Automatically do the math and give %. 

Item_No	January	FEB	MARCH	APRIL	MAY
Apple	500	600	300	200	250
Calculations		(600-500)/500 =20%	1. (500+600)/2 =550   2. (300-550)/550  =-45%	1. (500+600+300)/3 = 466.7                    2.(200-466.7)/466.7 = -57%	1. (600+300+200)/3=275                       2. (250-275)/275= -9%
Sales Growth		20%	-45%	-57%	-9%

1. Is it possible to days?

2. is there any another way to achieve this result?

Thank you,

Paul

[v-rongtiep-msft · Accepted solution]

Hi @Atinder ,

Please refer to my pbix file to see if it helps you.

Modify the before measure.

Measure 2 = 
VAR _last1value =
    CALCULATE (
        SUM(  Sales[SALES] ),
        FILTER (
            ALL ( 'Sales' ),
            Sales[Item] = SELECTEDVALUE ( Sales[Item] )
                && Sales[month]
                    = SELECTEDVALUE (Sales[month]) - 1
        )
    )
VAR _last2value =
   CALCULATE (
        SUM(  Sales[SALES] ),
        FILTER (
            ALL ( 'Sales' ),
            Sales[Item] = SELECTEDVALUE ( Sales[Item] )
                && Sales[month]
                    = SELECTEDVALUE (Sales[month]) - 2
        )
    )
VAR _last3value =
    CALCULATE (
        SUM(  Sales[SALES] ),
        FILTER (
            ALL ( 'Sales' ),
            Sales[Item] = SELECTEDVALUE ( Sales[Item] )
                && Sales[month]
                    = SELECTEDVALUE (Sales[month]) - 3
        )
    )
VAR _2re =
 CALCULATE (
        SUM(  Sales[SALES] ),
        FILTER (
            ALL ( 'Sales' ),
            Sales[Item] = SELECTEDVALUE ( Sales[Item] )
                && Sales[month]
                    = 2
        )
    )
    var _2ana=
     (_2re- _last1value ) / _last1value
VAR _3re =
CALCULATE (
        SUM(  Sales[SALES] ),
        FILTER (
            ALL ( 'Sales' ),
            Sales[Item] = SELECTEDVALUE ( Sales[Item] )
                && Sales[month]
                    = 3
        )
    )
    var _3ana=

    (
        _3re - ( ( _last1value + _last2value ) / 2 )
    ) / ( ( _last1value + _last2value ) / 2 )
    var _4re=CALCULATE (
        SUM(  Sales[SALES] ),
        FILTER (
            ALL ( 'Sales' ),
            Sales[Item] = SELECTEDVALUE ( Sales[Item] )
                && Sales[month]
                    = SELECTEDVALUE(Sales[month])
        )
    )
VAR bigthan4 =
    (
        _4re - ( ( _last1value + _last2value + _last3value ) / 3 )
    ) / ( ( _last1value + _last2value + _last3value ) / 3 )
RETURN
      IF (
        MAX ( Sales[month] ) = 1,
        BLANK (),
        IF (
            MAX ( Sales[month] ) = 2,
            _2ana,
            IF (
                MAX ( Sales[month] ) = 3,
                _3ana,
                IF ( MAX ( Sales[month] ) >= 4, bigthan4, 0 )
            )
        )
    )

There is currently no way to enter values as rows.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-rongtiep-msft]

Hi @Atinder ,

Please refer to my pbix file to see if it helps you.

Unpiovt the columns.

Then create a column first.

rankx =
RANKX (
    FILTER ( 'Table', 'Table'[item] = EARLIER ( 'Table'[item] ) ),
    'Table'[Index],
    ,
    ASC,
    DENSE
)

Then create a measure.

Measure =
VAR _last1value =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[item] = SELECTEDVALUE ( 'Table'[item] )
                && 'Table'[rankx]
                    = SELECTEDVALUE ( 'Table'[rankx] ) - 1
        )
    )
VAR _last2value =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[item] = SELECTEDVALUE ( 'Table'[item] )
                && 'Table'[rankx]
                    = SELECTEDVALUE ( 'Table'[rankx] ) - 2
        )
    )
VAR _last3value =
    CALCULATE (
        MAX ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[item] = SELECTEDVALUE ( 'Table'[item] )
                && 'Table'[rankx]
                    = SELECTEDVALUE ( 'Table'[rankx] ) - 3
        )
    )
VAR _2re =
    ( MAX ( 'Table'[Value] ) - _last1value ) / _last1value
VAR _3re =
    (
        MAX ( 'Table'[Value] ) - ( ( _last1value + _last2value ) / 2 )
    ) / ( ( _last1value + _last2value ) / 2 )
VAR bigthan4 =
    (
        MAX ( 'Table'[Value] ) - ( ( _last1value + _last2value + _last3value ) / 3 )
    ) / ( ( _last1value + _last2value + _last3value ) / 3 )
RETURN
    IF (
        MAX ( 'Table'[rankx] ) = 1,
        BLANK (),
        IF (
            MAX ( 'Table'[rankx] ) = 2,
            _2re,
            IF (
                MAX ( 'Table'[rankx] ) = 3,
                _3re,
                IF ( MAX ( 'Table'[rankx] ) >= 4, bigthan4, 0 )
            )
        )
    )

If I have misunderstood your meaning, please provide more details with your desired output and your pbix file without privacy information.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Atinder]

Hi @v-rongtiep-msft ,

Here is the link for my file.  I Want to get data in the  matrix format. 

 Compare monthly sales with average sales of last 90 days from that month.pbix

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[davehus]

Hi @Atinder , Based on the criteria above, please find the code. It uses dates in period do to a look back from the end of each month. You will need to add a month number to your table to use the code but hopefully it will do the trick for you.

Result =
VAR v_DIP1 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        DATESINPERIOD ( 'Table'[Date], PREVIOUSMONTH ( 'Table'[Date] ), -1, MONTH )
    )
VAR v_DIP2 =
    DIVIDE (
        CALCULATE (
            SUM ( 'Table'[Value] ),
            DATESINPERIOD ( 'Table'[Date], PREVIOUSMONTH ( 'Table'[Date] ), -2, MONTH )
        ),
        2
    )
VAR v_DIP3 =
    DIVIDE (
        CALCULATE (
            SUM ( 'Table'[Value] ),
            DATESINPERIOD ( 'Table'[Date], PREVIOUSMONTH ( 'Table'[Date] ), -3, MONTH )
        ),
        3
    )
RETURN
    IF (
        FIRSTNONBLANK ( 'Table'[Month], 1 ) = 1,
        BLANK (),
        IF (
            FIRSTNONBLANK ( 'Table'[Month], 1 ) = 2,
            DIVIDE ( [Sum Value] - v_DIP1, v_DIP1 ),
            IF (
                FIRSTNONBLANK ( 'Table'[Month], 1 ) = 3,
                DIVIDE ( [Sum Value] - v_DIP2, v_DIP2 ),
                DIVIDE ( [Sum Value] - v_DIP3, v_DIP3 )
            )
        )
    )

 Your last calculation is incorrect hence the difference in mine below. 

Did I help you today? Please accept my solution and hit the Kudos button.

[Atinder]

Hi @davehus ,

i am running into an error while  applying this code. 

AVERAGE =

VAR v_DIP1 =

    CALCULATE (

        SUM ( 'Sales'[Sales] ),

        DATESINPERIOD ( 'Calender'[Date], PREVIOUSMONTH ( 'Calender'[Date] ), -1, MONTH )

    )

VAR v_DIP2 =

    DIVIDE (

        CALCULATE (

            SUM ( 'Sales'[Sales] ),

            DATESINPERIOD ( 'Calender'[Date], PREVIOUSMONTH ( 'Calender'[Date] ), -2, MONTH )

        ),

        2

    )

VAR v_DIP3 =

    DIVIDE (

        CALCULATE (

            SUM ( 'Sales'[Sales] ),

            DATESINPERIOD ( 'Calender'[Date], PREVIOUSMONTH ( 'Calender'[Date] ), -3, MONTH )

        ),

        3

    )

RETURN

    IF (

        FIRSTNONBLANK ( 'Calender'[MONTH #], 1 ) = 1,

        BLANK (),

        IF (

            FIRSTNONBLANK ( 'Calender'[MONTH #], 1 ) = 2,

            DIVIDE ([Sales] - v_DIP1, v_DIP1 ),

            IF (

                FIRSTNONBLANK ( 'Calender'[MONTH #], 1 ) = 3,

                DIVIDE ( [Sum Sales] - v_DIP2, v_DIP2 ),

                DIVIDE ( [Sum Sales] - v_DIP3, v_DIP3 )

            )

        )

    )

[Atinder]

Hi @davehus,

I am comparing with Table called Sales and but with Sum of column sales.  But column is not showing up. 

[davehus]

Hi @Atinder , You need to replace sum sales with the Sum(Value you are comparing) so whatever column with the 500,600 etc data.

Did I help you today? Please accept my solution and hit the Kudos button.

[Atinder]

Hi @davehus,

I am comparing with Table called Sales and but with Sum of column sales.  But column is not showing up. 

[davehus]

Hi @Atinder ,

I've added a variable at the top for sales and changed it throughout the code, see if this works.

Result =
Var  v_Sales = Sum('Sales'[Sales])
VAR v_DIP1 =
    CALCULATE (
        v_Sales,
        DATESINPERIOD ( 'Sales'[Date], PREVIOUSMONTH ( 'Sales'[Date] ), -1, MONTH )
    )
VAR v_DIP2 =
    DIVIDE (
        CALCULATE (
            v_Sales,
            DATESINPERIOD ( 'Sales'[Date], PREVIOUSMONTH ( 'Sales'[Date] ), -2, MONTH )
        ),
        2
    )
VAR v_DIP3 =
    DIVIDE (
        CALCULATE (
           v_Sales,
            DATESINPERIOD ( 'Sales'[Date], PREVIOUSMONTH ( 'Sales'[Date] ), -3, MONTH )
        ),
        3
    )
RETURN
    IF (
        FIRSTNONBLANK ( 'Sales'[Month], 1 ) = 1,
        BLANK (),
        IF (
            FIRSTNONBLANK ( 'Sales'[Month], 1 ) = 2,
            DIVIDE ( v_Sales - v_DIP1, v_DIP1 ),
            IF (
                FIRSTNONBLANK ( 'Sales'[Month], 1 ) = 3,
                DIVIDE (v_Sales - v_DIP2, v_DIP2 ),
                DIVIDE ( v_Sales - v_DIP3, v_DIP3 )
            )
        )
    )

[Atinder]

Hi @davehus ,

Now I got a everything at 200%. Here is the link to the sample file. 

Compare monthly sales with average sales of last 90 days from that month.pbix

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.