Accumulated total of average per month/YTD, per week/YTD

[RMV]

Hi all,

Need advise on syntax to use to calculate below case.

I have a calculation of total figure of an average sales amount for all regions YTD.

I used this calculation to calculate the total figure: SUMX(VALUES(Region[RegionName]),[Average of Sales Amount YTD])

The average calculation is: CALCULATE([Total Amount],FILTER(Sales,AND(MIN(Sales[sales_date]),MAX(Sales[sales_date])))) / CALCULATE(DISTINCTCOUNT(Sales[sales_date]),FILTER(Sales,AND(MIN(Sales[sales_date]),MAX(Sales[sales_date]))))

Now, I need to create a comparison of the total figure of an average sales amount for all regions per end of month & per end of week. This is the condition I'm looking for (assuming that this graph/table is seen today 29-May-17):

Per month

Jan-17          (Total figure of average sales amount for all regions as of Jan-17)

Feb-17         (Total figure of average sales amount for all regions as of Feb-17)

Mar-17        (Total figure of average sales amount for all regions as of Mar-17)

Apr-17         (Total figure of average sales amount for all regions as of Apr-17)

29-May-17   Total figure of average sales amount for all regions as of Today 29-May-17)

Per week

same concept as above, but calculated per week

Please kindly help on how to calculate per month & per week figure.

Appreciate it

Regards,

[RMV]

Hi @v-ljerr-msft,

Sure. Sorry for not being detail on the case.

I currently have Sales Table

Date           Region             Sales Amount

1-Jan-17     A                      1000

1-Jan-17     B                       1200

1-Jan-17     C                       500

2-Jan-17     A                       1050

2-Jan-17     C                       700

3-Jan-17     B                       1500

etc

Currently I already have calculation of the total of average from each region YTD.

For example, this is the figure of 29-May-17 (consistent with the previous example that for example current date is 29-May), if we visualize it using Table

Region       Average from 1-Jan-17 to 30-May-17 per Region

A                1200

B                 1500 

C                 550

Total          3250 (1200 + 1500 + 550)

Then, what I'm looking for is the trend of the total of average above per month or until current date

X axis is month, the values in each month is

1. Total of average from 1-Jan to 31-Jan for Jan axis

2. Total of average from 1-Jan to 28-Feb for Feb axis

3. Total of average from 1-Jan to 31-Mar for Mar axis

4. Total of average from 1-Jan to 30-Apr for Apr axis

5. Total of average from 1-Jan to 29-May (current date) for May axis

6. moving forward

Looking forward for your help.

Thank you

[v-ljerr-msft]

Hi @RMV,

Could you try the formula below to see if it works in your scenario? 

Average of Sales Amount YTD =
VAR currentDate =
    MAX ( Sales[sales_date] )
RETURN
    CALCULATE (
        AVERAGE ( Sales[Sales Amount] ),
        FILTER ( ALL ( Sales ), Sales[sales_date] <= currentDate )
    )

Total Average = SUMX ( VALUES ( Region[RegionName] ), [Average of Sales Amount YTD] )

Regards

[RMV]

Hi @v-ljerr-msft,

It's not working 

This is the data sample

Date	Month	Region	Sales Amount
1-Jan-17	1	A	1000
1-Jan-17	1	B	1200
1-Jan-17	1	C	500
2-Jan-17	1	A	1050
2-Jan-17	1	C	700
3-Jan-17	1	B	1500
4-Jan-17	1	A	1100
4-Jan-17	1	B	1500
2-Feb-17	2	A	1300
2-Feb-17	2	C	400
5-Feb-17	2	A	1200
5-Feb-17	2	B	1700
5-Feb-17	2	C	600

The result i got by applying formula given from you is

January = 3206.25

February = 3173.08

While it should be:

January = 3050

February = 3155

This came from below calculation

Region      Jan Sales Amount                   Jan Average                       Jan-Feb Sales Amount                       Jan-Feb Average

                  total of sales amount per region in Jan   average of sales amount in Jan        total of sales amount per region in Jan-Feb          total of sales amount Jan-Feb

A               3150 (1000 + 1050 + 1100)              1050 (3150 / 3)                        5650 (1000 + 1050 + 1100 + 1300 + 1200)     1130 (5650 / 5)

B               4200 (1200 + 1500 + 1500)              1400 (4200 / 3)                        5900 (1200 + 1500 + 1500) + 1700)              1475 (5900 / 4)

C               1200 (500 + 700)                            600 (1200 / 2)                         2200 (500 + 700 + 400 + 600)                       550 (2200 / 4)

Total of average per region                     3050 (1050 + 1400 + 600)                                                                       3155 (1130 + 1475 + 550)

any advise?

[v-ljerr-msft · Accepted solution]

Hi @RMV,

My mistake! 

The formula below should work.

Average of Sales Amount YTD =
VAR currentDate =
    MAX ( Sales[Date] )
VAR currentRegion =
    FIRSTNONBLANK ( Sales[Region], 1 )
RETURN
    CALCULATE (
        AVERAGE ( Sales[Sales Amount] ),
        FILTER (
            ALL ( Sales ),
            Sales[Date] <= currentDate
                && Sales[Region] = currentRegion
        )
    )

Total Average = SUMX ( VALUES ( Sales[Region] ), [Average of Sales Amount YTD] )

Regards

[RMV]

Hi @v-ljerr-msft,

It works! Thanks a lot!

Regards,

[v-ljerr-msft]

Hi @RMV,

Could you post your table structures with some sample data and your expected result? So that we can better assist on this issue. 

Regards