Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Time Intelligence Functions alternative

Hello community,

 

I have a question

 

SAMEPERIODLASTYEAR function returns the exact value of the current measure on the context of the filter(year, quarter, month or day)

 

Now imagine that today = 16/05/2023 and I have a table at year/month level from 2023-01 to 2023-05.

 

What I want is dax to compute full month comparison based on max date from my dim date.

Basically, if my current max is 16-05-2023, sameperiodlastyear will work great from Jan-Apr, But in May it will compute full month from May-2022

 

How can I prevent that ?

Help
  • FreemanZ's avatar
    FreemanZ
    2 years ago

    Hi Anonymous ，

     

    not sure if i fully get you, but you can add additional date filter argument, like:

    SalesLY = 
VAR _lastday = MAXX(ALL(data[date]), data[date])   //similar to today()
VAR _result = CALCULATE(
    SUM(data[sales]),
    SAMEPERIODLASTYEAR(dates[date]),
    dates[date]<=EDATE(_lastday, -12)
)
RETURN _result

    for a data table like:

    date sales
    1/1/2022 1
    1/21/2022 1
    2/10/2022 1
    3/2/2022 1
    3/22/2022 1
    4/11/2022 1
    5/1/2022 1
    5/21/2022 1
    6/10/2022 1
    6/30/2022 1
    7/20/2022 1
    8/9/2022 1
    8/29/2022 1
    9/18/2022 1
    10/8/2022 1
    10/28/2022 1
    11/17/2022 1
    12/7/2022 1
    12/27/2022 1
    1/16/2023 1
    2/5/2023 1
    2/25/2023 1
    3/17/2023 1
    4/6/2023 1
    4/26/2023 1
    5/16/2023 1

     

    it worked like:

     

    find more in the attachment. 

     

     

1 Reply

  • FreemanZ's avatar
    FreemanZ
    Super User
    2 years ago

    Hi Anonymous ，

     

    not sure if i fully get you, but you can add additional date filter argument, like:

    SalesLY = 
VAR _lastday = MAXX(ALL(data[date]), data[date])   //similar to today()
VAR _result = CALCULATE(
    SUM(data[sales]),
    SAMEPERIODLASTYEAR(dates[date]),
    dates[date]<=EDATE(_lastday, -12)
)
RETURN _result

    for a data table like:

    date sales
    1/1/2022 1
    1/21/2022 1
    2/10/2022 1
    3/2/2022 1
    3/22/2022 1
    4/11/2022 1
    5/1/2022 1
    5/21/2022 1
    6/10/2022 1
    6/30/2022 1
    7/20/2022 1
    8/9/2022 1
    8/29/2022 1
    9/18/2022 1
    10/8/2022 1
    10/28/2022 1
    11/17/2022 1
    12/7/2022 1
    12/27/2022 1
    1/16/2023 1
    2/5/2023 1
    2/25/2023 1
    3/17/2023 1
    4/6/2023 1
    4/26/2023 1
    5/16/2023 1

     

    it worked like:

     

    find more in the attachment.