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 😄 
karo's avatar
karo
Icon for Advocate V rankAdvocate V
7 months ago

Thank you ALL for your inputs! Thanks to your comments I was able to find a way to re-write my measue. 

 
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 months ago

    Hi karo ,

    We really appreciate your efforts and for letting us know the update on the issue.

    If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.

    Please continue using fabric community forum for your further assistance.

    Thank you for your understanding and assistance.

     

    • karo's avatar
      karo
      Icon for Advocate V rankAdvocate V
      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 😄 
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 months ago

        Hi karo ,

        Glad you were able to resolve your issue.

        Please continue using fabric community forum for your further assistance.

        Thank you