Forum Discussion

jmolina's avatar
jmolina
Frequent Visitor
4 years ago

Previous Year to Date until certain Date.

Hello everyone, along with saying hello, I hope you can help me with the following:

 

I have a dataset that has several million pieces of data, from which I need to calculate the YTD and Previous YTD. The problem I have is that the YTD is not up to the current date, but it is up to the maximum date that the "RETAIL" has, for example

 

retail     year       date              current date
retail1   2021    01-16-2022    01-17-2022
retail1   2022    01-15-2022    01-17-2022 (maximum date or current date)

 

I currently have this formula for the PYTD

 

PYTD=
CALCULATE ([YTD],
FILTER (
SAMEPERIODLASTYEAR ( CALENDAR[Date] ),
CALENDAR[Date]
< DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
)
)

 

This dax gives me the PYTD as a result until 01-16-2021, it should be equal until 01-15-2021 according to the YTD max date.

 

Please Help.

Christian.

Need Help

11 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Your question is not clear.  Share some data and show the expected result clearly (with an explanation).

    • jmolina's avatar
      jmolina
      Frequent Visitor
      4 years ago

      Hi, thanks for response:

       

      I have 4 "BRANDS" within the data, which have sales for both 2021 and 2022, I need the PYTD to be calculated until the last date indicated by the YTD, for example:

       

       

      The PYTD of "BRAND4" should be until the same day that the YTD indicates, that is, until 01-22-2022, my current formula calculates me until 01-23-2022 (considers the maximum of the date of 2021, and it should consider the maximum date of 2022 as limit per different BRAND)

       

      Thanks in advance