Forum Discussion

afeef's avatar
afeef
New Member
11 months 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]), 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)

 

  • johnt75's avatar
    johnt75
    11 months 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

5 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    11 months 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.

     

     

     

  • johnt75's avatar
    johnt75
    Super User
    11 months 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
  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    11 months ago

    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.

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    11 months ago

    Hello afeef,

    Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

    Thank you.

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    10 months ago

    Hello afeef,

    We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

    Thank you.