Calculate previous year of aggregated sum

[simber]

Hi,

I have a measure which is the cumulative sum of another measure for a selected period of time, which I calculate as below. 
How do I create a measure which calculates the previous year for the agg_buy measure based on the same selection?

TimeQuery[YearMonthDay] is a date column which contains the Year, month and day.

agg_buy = 
sumx(
    FILTER(
        ALLSELECTED(TimeQuery),
        TimeQuery[YearMonthDay] <=MAX(TimeQuery[YearMonthDay]) 
    
    ),


    [BUY] 
)

[Icey]

Hi @simber ,

How about this?

agg_buy = 
sumx(
    FILTER(
        ALLSELECTED(TimeQuery),
        TimeQuery[YearMonthDay] < MAX(TimeQuery[YearMonthDay]) -----------use "<" instead of "<="
    
    ),


    [BUY] 
)

Best Regards,

Icey

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

[simber]

Hi @Icey !

How would this aggregate the previous year? It only excludes the current selected value in the [YearMonthDay] column and doesn't aggregate the previous year.

[Icey]

Hi @simber ,

Sorry, some misunderstandings. If your [YearMonthDay] column is Date/DateTime type, have you tried SAMEPERIODLASTYEAR function? 

Best Regards,

Icey

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

[simber]

I tried using it by replacing the max function so it looks like this:

agg_buy = 
sumx(
    FILTER(
        ALLSELECTED(TimeQuery),
        TimeQuery[YearMonthDay] < SAMEPERIODLASTYEAR(TimeQuery[YearMonthDay]) 
    
    ),


    [BUY] 
)

But all I got was a single value aggregation for the same period last year, which i can't display in a line chart.

[Icey]

Hi @simber ,

How about this?

agg_buy = 
CALCULATE( [BUY],SAMEPERIODLASTYEAR(TimeQuery[YearMonthDay]) 
    

Best Regards,

Icey

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

[simber]

I doesn't work either. The [BUY] measure consists of serveral summarized columns and is not a cumulative sum. That's why the original measure looks like it does, as it creates a cumulative sum which can be displayed in a line chart.

[Icey]

Hi @simber ,

I create a simple sample and find that replace "ALLSELECTED" in your orginal "agg_buy" measure with "ALL" and create another measure like below:

LY = CALCULATE ( [agg_buy], SAMEPERIODLASTYEAR(TimeQuery[YearMonthDay]))

And get this result:

Best Regards,

Icey

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

[simber]

Your line graph looks just like I want the end result to look like but it still doesn't work.

I opened your pbix file and did see that your TY measure is calculated this way:

TY = CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Dates'[Date])))

But as you see in my version of TY (as agg_buy=TY) below, it in itself contains a measure. 

agg_buy =
sumx(
FILTER(
ALLSELECTED(TimeQuery),
TimeQuery[YearMonthDay] <=MAX(TimeQuery[YearMonthDay])
),
[BUY]
)

The measure [BUY] is used to summarize diffenerent columns depending on the selections from another slicer. It looks like this:

BUY = IF( HASONEVALUE('GridArea'[GridArea]),
switch(VALUES('GridArea'[GridArea]),
"SE1",(sum(trades_se1[asset1])+sum(trades_se1[asset2])),
"SE2",(sum(trades_se2[asset1])+sum(trades_se2[asset2])),
"SE3",(sum(trades_se3[asset1])+sum(trades_se3[asset2])),
"SE4",(sum(trades_se1[asset1])+sum(trades_se1[asset2])),

"Total",
(sum(trades_se1[asset1])+sum(trades_se1[asset2]))+
(sum(trades_se2[asset1])+sum(trades_se2[asset2]))+
(sum(trades_se3[asset1])+sum(trades_se3[asset2]))+
(sum(trades_se4[asset1])+sum(trades_se4[asset2]))
,
(sum(trades_se1[asset1])+sum(trades_se1[asset2]))
)

I also tried to replace SUM('Table'[Value]) in your measure TY with my [BUY] measure but I did not see any values when I put it in a line chart.

[Icey]

Hi @simber ,

Use CALCULATE as my expression has no difference between yours. It is unnecessary to change it.

Does your "agg_buy" measure give the expected result of this year? 

agg_buy =
sumx(
FILTER(
ALLSELECTED(TimeQuery),
TimeQuery[YearMonthDay] <=MAX(TimeQuery[YearMonthDay])
),
[BUY]
)

If it does, please modify "agg_buy" measure by replacing "ALLSELECTED" with "ALL", then, it will look like below. If there are some categories, use ALLEXCEPT instead of ALL.

agg_buy =
sumx(
FILTER(
ALL(TimeQuery),
TimeQuery[YearMonthDay] <=MAX(TimeQuery[YearMonthDay])
),
[BUY]
)

After that, create last year measure like so:

agg_buy_LY = CALCULATE ( [agg_buy], SAMEPERIODLASTYEAR(TimeQuery[YearMonthDay]))

Hope I explain clearly.

Can this work?

If it doesn't work, could you share us a sample without sensitive information or real data for test?

Best Regards,

Icey

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