SUM multiple month values over years

[Anonymous]

I'm trying to sum multiple month values over several years e.g. Jan 2007, Jan 2008, Jan 2009.

I'm using the following formula however when I try push the date forward 12 months it comes back to the orignial value e.g. (Jan 2007) + Jan 2007 instead of (Jan 2007 + Jan 2008)

Is there a way to achieve multiple Jan additions?

I am using Alberto Ferrari's "Rolling Average" file as a sample template

https://www.sqlbi.com/wp-content/uploads/Computing-rolling-average-in-DAX-sample.zip

My DAX is

Aggregation 2 =
CALCULATE (
 SUMX (
    Sales,
    Sales[Quantity] * Sales[Net Price]
 ),
 ALLEXCEPT (
    'Date',
    'Date'[Month]
 ),DATESINPERIOD('Date'[Date],FIRSTDATE('Date'[Date]),1,MONTH))

+

CALCULATE (
 SUMX (
    Sales,
    Sales[Quantity] * Sales[Net Price]
 ),
 ALLEXCEPT (
    'Date',
    'Date'[Month]
 ),DATESINPERIOD('Date'[Date],FIRSTDATE('Date'[Date]),1,MONTH),EDATE('Date'[Date],12))

[v-janeyg-msft]

Hello @Anonymous 

Sorry I'm late. 

Accoding to the field used in the matrix visual, I create the measure like this:

Aggregation 3 =
IF (
    ISINSCOPE ( 'Date'[Month] ),
    SUMX (
        FILTER (
            ALL ( Sales ),
            YEAR ( [Order Date] ) <= SELECTEDVALUE ( 'Date'[Calendar Year Number] )
                && MONTH ( [Order Date] ) = SELECTEDVALUE ( 'Date'[Month Number] )
        ),
        Sales[Quantity] * Sales[Net Price]
    ),
    SUMX (
        FILTER (
            ALL ( Sales ),
            YEAR ( [Order Date] ) <= SELECTEDVALUE ( 'Date'[Calendar Year Number] )
        ),
        Sales[Quantity] * Sales[Net Price]
    )
)

The hierarchy context of matrix is different, so the total must be calculated separately, use isinscope() funcion.

Reference:ISINSCOPE function (DAX) - DAX | Microsoft Docs

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[Anonymous]

This is excellent thank you very very much for the time spent on this, I'm new to the ISINSCOPE function.

One last question is it possible to tweak the formula to return the max value rather than cumulative?

[v-janeyg-msft]

@Anonymous  What is the logic of the maximum? The yellow part you marked is not the maximum. What should the result be.

[Anonymous]

I'm trying to get the the sum of each month across years (Total Column below(this is what I meant by the max), apologies if I haven't explained it properly.

	2007	2008	2009	Total Column
Jan	794248.24	656766.69	580901.05	2031915.98
Feb	891135.91	600080.00	622581.14	2113797.05
Mar	961289.24	559538.52	496137.87	2016965.62
Apr	1128104.82	999667.17	678893.22	2806665.2
May	936192.74	893231.96	1067165.23	2896589.93
Jun	982304.46	845141.60	872586.2	2700032.26
Jul	922542.98	890547.41	1068396.58	2881486.97
Aug	952834.59	721560.95	835707.46	2510103.00
Sep	1009868.98	963437.23	709610.40	2682916.61
Oct	914273.54	719792.99	806738.22	2440804.75
Nov	825601.87	1156109.32	868164.01	2849875.20
Dec	991548.75	921709.14	746933.50	2660191.40

I know I have these results in the column [Aggregation] (highlighted above)

Aggregation = 
CALCULATE (
 SUMX (
 Sales,
 Sales[Quantity] * Sales[Net Price]
 ),
 ALLEXCEPT (
 'Date',
 'Date'[Month]
 ))

however when I filter the year e.g. 2007 it remains as these MAX numbers where I would like the ALLEXCEPT function to be a bit more dynamic. So with no date filters applied return [Total Column] and then when I pick the year e.g. 2007 it will change to just those values

	2007	Total Column
Jan	794248.24	2031915.98
Feb	891135.91	2113797.05
Mar	961289.24	2016965.62
Apr	1128104.82	2806665.2
May	936192.74	2896589.93
Jun	982304.46	2700032.26
Jul	922542.98	2881486.97
Aug	952834.59	2510103.00
Sep	1009868.98	2682916.61
Oct	914273.54	2440804.75
Nov	825601.87	2849875.20
Dec	991548.75	2660191.40

[v-janeyg-msft · Accepted solution]

@Anonymous 

Try:

Aggregation 3 =
IF (
    ISINSCOPE ( 'Date'[Month] ),
    CALCULATE (
        SUMX ( Sales, Sales[Quantity] * Sales[Net Price] ),
        ALLEXCEPT ( 'Date', 'Date'[Month] )
    ),
    SUMX (
        FILTER (
            ALL ( Sales ),
            YEAR ( [Order Date] ) <= SELECTEDVALUE ( 'Date'[Calendar Year Number] )
        ),
        Sales[Quantity] * Sales[Net Price]
    )
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,

Community Support Team _ Janey

[Anonymous]

Create a measure based on month not at all the date. it will work I had the same issue.

[Anonymous]

Good advice. Tried this but still the same Sales vs. Date table issue.

[v-janeyg-msft]

Hi, @Anonymous 

Do you want to calculate like below?

You can try:

Aggregation 2 =
SUMX (
    FILTER (
        ALL ( Sales ),
        YEAR ( [Date] ) <= YEAR ( SELECTEDVALUE ( Sales[Date] ) )
            && MONTH ( [Date] ) = MONTH ( SELECTEDVALUE ( Sales[Date] ) )
    ),
    Sales[Quantity] * Sales[Net Price]
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,

Community Support Team _ Janey

[Anonymous]

Thank you for the reply @v-janeyg-msft . I've tried to incorporate this measure into the sample but no luck. The Sales table only has an 'Sales'[Order Date] and I have been using 'Date'[Date] for the visual above but I can't bring 'Date'[Date]' into the measure:

Aggregation 2 = 

SUMX (
 FILTER (
 ALL ( Sales ),
 YEAR ( [Order Date] ) <= YEAR ( SELECTEDVALUE (Sales[Order Date] ) )
 && MONTH ( [Order Date] ) = MONTH ( SELECTEDVALUE ( Sales[Order Date] ) )
 ),
 Sales[Quantity] * Sales[Net Price]
)

Do I need to cross filter somehow? From your visual above is there a pbix you can share?

[v-janeyg-msft]

Hi, @Anonymous  

The picture is yours, I just ask about your needs. You didn’t provide a sample file. How could I have it? I iust wrote the code according to your description.

If you still need help, please upload some insensitive data samples and expected output, So we can help you soon.

Best Regards,

Community Support Team _ Janey

[Anonymous]

Apologies.

Please find link attached to file.

https://www.dropbox.com/s/giyeda55w6ybv7f/Monthly%20Average%20Over%20Years.pbix?dl=0

[v-janeyg-msft]

@Anonymous  Sorry, I'm too busy today and I will get back to you tomorrow.

[amitchandak]

@Anonymous , with help from date table, You can try like

example

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

[Anonymous]

Thanks @amitchandak but it's not quite what I'm looking for.

I'm trying to group all of them together rather than just obtain a 'Date'[Date],-1,YEAR)