How can I create a calculated column using SAMEPERIODLASTYEAR/DATEADD + FILTER?

[HamidBee]

Hi All,

I'll kindlly like to ask how I can calculate the value for the same period last year for each category. So  values for A,B,C & D. I think I need to be applying filters here but I'm not too sure. Any help would be greatly appreciated.

This is what I have so far but I'm sure I'm way off. 

 

[Anonymous · Accepted solution]

Hi @HamidBee ,

I have create a simple sample. Please have a try.

Create a column first.

year = YEAR('Table'[Date])

Then create a measure.

Measure =
VAR this_year =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[year] = SELECTEDVALUE ( 'Table'[year] )
                && 'Table'[Attribute] = SELECTEDVALUE ( 'Table'[Attribute] )
        )
    )
VAR previous_year =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[year]
                = SELECTEDVALUE ( 'Table'[year] ) - 1
                && 'Table'[Attribute] = SELECTEDVALUE ( 'Table'[Attribute] )
        )
    )
RETURN
    this_year - previous_year

 I have also create a column.

Column =
VAR _beforeyear =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[year]
                = EARLIER ( 'Table'[year] ) - 1
                && 'Table'[Attribute] = EARLIER ( 'Table'[Attribute] )
        )
    )
VAR thisyear =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Attribute] = EARLIER ( 'Table'[Attribute] )
                && 'Table'[year] = EARLIER ( 'Table'[year] )
        )
    )
RETURN
    thisyear - _beforeyear

Please refer to it to see if it helps you.

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.

[VahidDM]

HI @HamidBee 

Try this:
New Column = 

Var _DT = Summary Table[Date]
Var _Y = Year(_DT)
Var _M = Month(_DT)
Var _D = Day(_DT)
Var _LYD = Date(_Y-1,_M,_D)
Var _S = sum(Summary Table[value])
Var _LY = calcultae(_S,filter(all(Summary Table),Summary Table[Date]=_LYD&&Summary Table[Attribute]=Earlier(Summary Table[Attribute]))
return
_S-_LY

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[HamidBee]

This is what I get when I try to type the formula in.

 

[VahidDM]

Try this:

New Column =
VAR _DT = 'Summary Table'[Date]
VAR _Y =
    YEAR ( _DT )
VAR _M =
    MONTH ( _DT )
VAR _D =
    DAY ( _DT )
VAR _LYD =
    DATE ( _Y - 1, _M, _D )
VAR _S =
    SUM ( 'Summary Table'[value] )
VAR _LY =
    CALCULATE (
        SUM ( 'Summary Table'[value] ),
        FILTER (
            ALL ( 'Summary Table' ),
            'Summary Table'[Date] = _LYD
                && 'Summary Table'[Attribute] = EARLIER ( 'Summary Table'[Attribute] )
        )
    )
RETURN
    _S - _LY

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[HamidBee]

I received the following error when I pasted this in:

 

[VahidDM]

😁Sorry mate, try this:

New Column =
VAR _DT = 'Summary Table'[Date]
VAR _Y =
    YEAR ( _DT )
VAR _M =
    MONTH ( _DT )
VAR _D =
    DAY ( _DT )
VAR _LYD =
    DATE ( (_Y - 1), _M, _D )
VAR _S =
    SUM ( 'Summary Table'[value] )
VAR _LY =
    CALCULATE (
        SUM ( 'Summary Table'[value] ),
        FILTER (
            ALL ( 'Summary Table' ),
            'Summary Table'[Date] = _LYD
                && 'Summary Table'[Attribute] = EARLIER ( 'Summary Table'[Attribute] )
        )
    )
RETURN
    _S - _LY

Make sure Type of your Date column is DATE,

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[HamidBee]

I checked the data type and it's set to 'date'. That column has a relationship with a date table that is part of the model I'm not sure if that has anything to do with it. I'm currently getting the same error when I paste the above code.

An argument of function 'DATE' has the wrong data type or the result is too large or too small.

[VahidDM]

@HamidBee Can you share your PBIX file?

Upload that on https://wetransfer.com/

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[HamidBee]

No problem. Please find attached the example file here below:

https://www.mediafire.com/file/eptjd7i8636ba4q/Example_File.pbix/file

I'm only interested in the years spanning 2020 and 2021. Their is data for 2022 which I am not calculating for but I wouldn't want the data to be removed. 

Thanks alot for your help

[VahidDM]

HI @HamidBee 

Just add ' before and after the table name, or remove table name and select that again.

should be: 'Summary Table'

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[HamidBee]

Apologies I should have noticed that. I've entered the code now and this is the new error I'm getting:

 

New Column =

Var _DT = 'Summary Table'[Date]

Var _Y = Year(_DT)

Var _M = Month(_DT)

Var _D = Day(_DT)

Var _LYD = Date(_Y-1,_M,_D)

Var _S = sum('Summary Table'[value])

Var _LY = CALCULATE(_S,filter(all('Summary Table'),[Date]=_LYD&&'Summary Table'[Attribute]=Earlier('Summary Table'[Attribute]))

return

_S-_LY

[Daryl-Lynch-Bzy]

@HamidBee - What is the "value" referenced in the second part of the formula.  Should be SUM ( 'Summary Table'[VALUE] as well?.    Try using PARALLELPERIOD ( 'Calendar'[Date] , -1 , YEAR )  PARALLELPERIOD – DAX Guide

[HamidBee]

I tried using the following formula:

Column 2 = Calculate(sum('Summary Table'[Value]))-calculate(sum('Summary Table'[Value]),PARALLELPERIOD('Calendar'[Date],-1,YEAR))

but it returns the exact same values as those already in the current value column.

[ALLUREAN]

Hi, @HamidBee 

Can you try this:

Column =

SUM('Summary Table'[Value]) 

-

CALCULATE(
SUM('Summary Table'[Value]),
ALLEXCEPT('Summary Table', 'Summary Table'[Attribute]),
SAMEPERIODLASTYEAR('Calendar'[Date])
)

Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.

Appreciate your Kudos !!!

https://allure-analytics.com/

https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA

https://www.linkedin.com/company/77757292/

[HamidBee]

I just tried the formula and got one value for all the rows.