Forum Discussion

Fusilier's avatar
Fusilier
Icon for Helper III rankHelper III
3 years ago
Solved

SAMEPERIODLASTYEAR not working.

Hi,

Trying to create a DAX command using this function.

My data is as follows:

The Month column is formatted as Date.

If I take out the SAMEPERIODLASTYEAR element it returns the value for the current month OK. But as soon as I add in SAMEPERIODLASTYEAR it returns a blank field.

Can anybody see what I'm doing wrong here?

 

  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Fusilier 

    try like:

    Prev Year = 
CALCULATE(
    SUM(data[Incident]),
    FILTER(
        ALL(data),
        data[month]=EDATE(EARLIER(data[Month]), -12)
    ),
    ALLEXCEPT(data, data[Location])
)

    or 

    Prev Year2 = 
SUMX(
    FILTER(
        data,
        data[month]=EDATE(EARLIER(data[month]), -12)
            &&data[location]=EARLIER(data[location])
    ),
    data[Incident]
)

     

    it worked like:

2 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Fusilier 

    try like:

    Prev Year = 
CALCULATE(
    SUM(data[Incident]),
    FILTER(
        ALL(data),
        data[month]=EDATE(EARLIER(data[Month]), -12)
    ),
    ALLEXCEPT(data, data[Location])
)

    or 

    Prev Year2 = 
SUMX(
    FILTER(
        data,
        data[month]=EDATE(EARLIER(data[month]), -12)
            &&data[location]=EARLIER(data[location])
    ),
    data[Incident]
)

     

    it worked like:

  • Fusilier's avatar
    Fusilier
    Icon for Helper III rankHelper III
    3 years ago

    Apologies, my fault. Solution 2 works like a dream!

     

    THANK YOU!