Current Year vs LY Comparison

[afeef]

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]), SAMEPERIODLASTYEAR('Calendar'[SalesDate])).

Now the issue is that, when display the data in the graph, lets say for the period of 1/1/2025 to 9/9/2025, the currSales is taken till 9/9 but the LY data is shown till 30/9(end of the month).

Any ideas on how i can limit the LY data to follow the slicer values? (there is a date slicer with 'Calendar'[SalesDate]) 
LY expected value for September should be similar to TY value (+- 0.5M)

[v-ssriganesh]

Hi @afeef,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @johnt75 & @rohit1991 for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[johnt75]

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]

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.