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

[karo]

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 visual).

I have found a solution (Total Sales YTD PY (2)), but it seems to be quite harsh. Can you help me optimize Total Sales YTD PY (2)?

FROM (not working for non contiguos selectios): 

Total Sales YTD PY (1)=

VAR TotalSalesYTDPY =

        TOTALYTD(

            [Total Sales],

            SAMEPERIODLASTYEAR(Calendar[Calendar Date])

        )

RETURN

     IF(

        ISBLANK(TotalSalesYTDPY),

        "N/A for PY",

        TotalSalesYTDPY

    )

IDEA (working for non contiguos selectios):

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 =        

        TOTALYTD(

            [Total Sales],

            DATESBETWEEN(

            Calendar[Calendar Date],

            StartDateOfPreviousYear,

            PreviusYearMaxSelectedDateincludingLunarYear))        

RETURN

       IF(

        ISBLANK(TotalSalesYTDPY),

        "N/A for PY",

        TotalSalesYTDPY

       )

[karo]

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

[v-nmadadi-msft]

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 · Accepted solution]

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]

Hi @karo ,

Glad you were able to resolve your issue.

Please continue using fabric community forum for your further assistance.

Thank you

[FBergamaschi]

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

[JamieHolding]

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
    )

[FreemanZ]

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

       )

[Amankumar007]

YTD Previous Year based on the max selected date (ignores gaps)

This builds PY YTD up to the same day as your current selection’s max date doesn’t require a contiguous date range.

 Assumes a proper, continuous Date table 'Calendar' with a [Date] column

Total Sales YTD PY =

VAR MaxSelectedDate =

    MAXX(ALLSELECTED('Calendar'[Date]), 'Calendar'[Date])

VAR PY_EndDate = EDATE(MaxSelectedDate, -12)

VAR PY_StartDate = DATE(YEAR(PY_EndDate), 1, 1)

RETURN

CALCULATE(

    [Total Sales],

    DATESBETWEEN('Calendar'[Date], PY_StartDate, PY_EndDate)

)

Why this works:

• ALLSELECTED gets the max date from the current user context (even if the selection is disjoint).
• We then explicitly construct the previous year YTD window with DATESBETWEEN, which doesn’t assume contiguity.