Show YOY Cumulative values on line chart

[vicsinc]

I have a line chart that shows cumulative number of leads received vs target leads. Nothing fancy.

I wish to add a third line to show the Previous Year cumulative leads received.

The cumulative measure is calculated as follows:

CumLeadsActual = CALCULATE(SUM('Leads'[Leads Actual]),FILTER(ALLSELECTED('Leads'),'Leads'[Month]<=MAX('Leads'[Month])&&'Leads'[IsCurrentMonth]="Past Month"))

When I try to use Same Period Last Year, it's not working as expected as it's showing me monthly figures instead of cumulative

LYCumLeads = CALCULATE([CumLeadsActual], SAMEPERIODLASTYEAR('Leads'[Month]))

Please could somebody help?

Thank you

Vic

[v-kongfanf-msft · Accepted solution]

Hi @vicsinc ,

You can try below formula to create measures:

LYCumLeads = 
VAR CurrentMonth = MAX('Leads'[Month])
RETURN
CALCULATE(
    [CumLeadsActual],
    FILTER(
        ALLSELECTED('Leads'),
        'Leads'[Month] <= EOMONTH(CurrentMonth, -12)
    )
)

CumLeadsActual = 
CALCULATE(
    SUM('Leads'[Leads Actual]),
    FILTER(
        ALLSELECTED('Leads'),
        'Leads'[Month] <= MAX('Leads'[Month]) && 'Leads'[IsCurrentMonth] = "Past Month"
    )
)

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.