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

Hi karo,

what are you looking for? Performance improvement? Shorter code? Bug fixes?

 

Without this information, I am not sure how to help you.

 

Surely, instead of writing this

 

VAR MaxSelectedDate =
        MAXX(
            ALLSELECTED('Calendar'),
            Calendar[Calendar Date]
        )
 
it is better to write this (no AI will intercept this subtle thing)
 
VAR MaxSelectedDate =
        CALCULATE ( 
             MAX ( Calendar[Calendar Date] ),
            ALLSELECTED('Calendar'),
        )
 
but more than that I would not know, the code looks OK to me, maybe the lunar part can be optimized a bit but I doubt that will improve a lot as, again, the code looks pretty much OK
 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI