Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Same Period Last Year

I have Date Dim and SPLY, in which you can see dates. Check image below.

I want to fetch date till 22-01-2020(and not till 31-01-2020) in Same Period Last Year because today's date is 22-01-2021.

Check below DAX measure I have written and the result of it in attached image.

_Measure =
CALCULATE(
CONCATENATEX (
VALUES(Dates[Date]),
Dates[Date],
UNICHAR(10)
),
SAMEPERIODLASTYEAR(Dates[Date])
)

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try one of the 2. Make sure the date table is marked as a date table

     

    _Measure =
    CALCULATE(
    CONCATENATEX (
    VALUES(Dates[Date]),
    Dates[Date],
    UNICHAR(10)
    ),
    dateadd(Dates[Date],-1, year)
    )

     

    or

     

    _Measure =
    CALCULATE(
    CONCATENATEX (
    VALUES(Dates[Date]),
    Dates[Date],
    UNICHAR(10)
    ),
    datesmtd(dateadd(Dates[Date],-1, year))
    )

     

    Also refer this formula, You can embed you formula with filter of date , just an example. check how date filter of today -1 year is forced

     

    LYMTD QTY forced=
    var _max = date(year(today())-1,month(today()),day(today()))
    return
    if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
    //OR
    //CALCULATE(Sum('order'[Qty]),DATESMTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
    //TOTALMTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)