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 😄 
FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
7 months ago

hi karo ,

 

try like:

 

Total Sales YTD PY (2)=

 

 

VAR MaxSelectedDate =

        MAXX(

            ALLSELECTED('Calendar'),

            Calendar[Calendar Date]

        )

 

 

VAR PreviousYearMaxSelected = YEAR(MaxSelectedDate) - 1      

 

 

VAR PreviousYearMaxSelectedDate = DATE(PreviousYearMaxSelected, MONTH(MaxSelectedDate), DAY(MaxSelectedDate))

 

 

VAR PreviusYearMaxSelectedDateincludingLunarYear =

        IF(

           PreviousYearMaxSelectedDate = DATE(PreviousYearMaxSelected,2,28),

           DATE(PreviousYearMaxSelected,2,29),

           PreviousYearMaxSelectedDate

        )

 

 

VAR StartDateOfPreviousYear = DATE(PreviousYearMaxSelected,1,1)

 

 

VAR TotalSalesYTDPY =    

    CALCULATE(

        [Total Sales],

            FILTER(

           ALLSELECTED(Calendar[Calendar Date]),

            Calendar[Calendar Date]>=StartDateOfPreviousYear 

&& Calendar[Calendar Date]<= PreviusYearMaxSelectedDateincludingLunarYear)

)        

       

RETURN

       IF(

        ISBLANK(TotalSalesYTDPY),

        "N/A for PY",

        TotalSalesYTDPY

       )