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 to...
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. 

     

     

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.