Forum Discussion

afeef's avatar
afeef
New Member
1 year ago
Solved

Current Year vs LY Comparison

Hi Community,   So currently I'm trying to display current Sales vs LY Sales in a clustered column chart. I have created a measure for LY:  NetSales LY = CALCULATE(sum('Current Sales'[NetSales])...
  • johnt75's avatar
    johnt75
    1 year ago

    You could try

    NetSales LY =
VAR _Dates =
    CALCULATETABLE (
        DATESYTD ( 'Calendar'[SalesDate] ),
        TREATAS ( { TODAY () }, 'Calendar'[SalesDate] )
    )
VAR Result =
    CALCULATE (
        SUM ( 'Current Sales'[NetSales] ),
        KEEPFILTERS ( SAMEPERIODLASTYEAR ( _Dates ) )
    )
RETURN
    Result
rohit1991's avatar
rohit1991
Icon for Super User rankSuper User
1 year ago

Hi afeef 

To align LY with TY, you need to restrict LY to the same “last available date” in the current year. You can do this by comparing against MAX( 'Calendar'[SalesDate] ). For example:

NetSales LY Aligned =
CALCULATE(
    SUM ( 'Current Sales'[NetSales] ),
    DATESBETWEEN(
        'Calendar'[SalesDate],
        DATEADD( MIN ( 'Calendar'[SalesDate] ), -1, YEAR ),
        DATEADD( MAX ( 'Calendar'[SalesDate] ), -1, YEAR )
    )
)

This way, if your current year slicer is 1/1/2025 → 9/9/2025, then LY will only calculate for 1/1/2024 → 9/9/2024, not the full month of September.