Forum Discussion

karo's avatar
karo
Advocate 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
Advocate 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 😄 
v-nmadadi-msft's avatar
v-nmadadi-msft
Community Support
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