Forum Discussion

karo's avatar
karo
Icon for Advocate V rankAdvocate V
7 months ago
Solved

Substitute for SAMEPERIODLASTYEAR() which will work for non contiguous selections

Hi,   I am trying to find a substitute for SAMEPERIODLASTYEAR() to calculate Total Sales YTD PY, which will be working also for  non contiguous selections (to avoid Error fetching data for this vis...
Need Help
  • karo's avatar
    karo
    7 months ago

    Sure, please find my changes implemented to Total Sales YTD PY (2) thanks to the comments in this thread. 

     

    Total Sales YTD PY (2)=

    [...]

    VAR PreviusYearMaxSelectedDateincludingLunarYear =

            IF(
               PreviousYearMaxSelectedDate = DATE(PreviousYearMaxSelected,2,28),
               DATE(PreviousYearMaxSelected,2,29),
               PreviousYearMaxSelectedDate
            )

    VAR PreviusYearMaxSelectedDate_IncludingLunarYear =

            IF(
                MaxSelectedDate = DATE(YEAR(MaxSelectedDate),2,28),
                EDATE(PreviousYearMaxSelectedDate,0),
                PreviousYearMaxSelectedDate
            )

     

    --> As I want only to get 29th of February for the previous year if it exists and if max selected date = 28th of February, in other cases return me the exact date. To get the same behavoiur as build-in for SAMEPERIODLASTYEAR() in case of Lunar Year. 

     

    [...]


    VAR TotalSalesYTDPY =        

            TOTALYTD(
                [Total Sales],
                DATESBETWEEN(
                Calendar[Calendar Date],
                StartDateOfPreviousYear,
                PreviusYearMaxSelectedDateincludingLunarYear))        


    VAR TotalSalesYTDPY =        

            CALCULATE(
                [Total Shipments],
                DATESBETWEEN(
                Calendar[Calendar Date],
                StartDateOfPreviousYear,
                PreviusYearMaxSelectedDate_IncludingLunarYear))     

     

    [...]

     --> As TOTALYTD( [..] , DATESBETWEEN([...])) has a syntax issue even if the formula is not showing an error. Simply speaking we should never nest DATESBETWEEN() inside TOTALYTD(). 

     

     

     
    Hope it helps someone 😄 
JamieHolding's avatar
JamieHolding
Icon for Resolver I rankResolver I
7 months ago

I refined your code with AI assistance (so please check carefully). I am pretty sure you could use EOMONTH() instead of your lunar year calculations. Let me know if this throws up errors.

 
 
Total Sales YTD PY =
VAR MaxSelectedDate = 
    MAXX(
        ALLSELECTED('Calendar'),
        Calendar[Calendar Date]
    )
VAR PreviousYear = YEAR(MaxSelectedDate) - 1
VAR YTDEndDatePY = 
    DATE(
        PreviousYear,
        MONTH(MaxSelectedDate),
        DAY(
            EOMONTH(
                DATE(PreviousYear, MONTH(MaxSelectedDate), 1),
                0
            )
        )
    )
VAR TotalSalesYTDPY =
    TOTALYTD(
        [Total Sales],
        DATESBETWEEN(
            Calendar[Calendar Date],
            DATE(PreviousYear, 1, 1),
            YTDEndDatePY
        )
    )
RETURN
    IF(
        ISBLANK(TotalSalesYTDPY),
        "N/A for PY",
        TotalSalesYTDPY
    )