ytd and lytd without selecting a year

[alihijazi]

Hello

I got transacions for the year 2015 from Jan till Dec

and for the year 2016 from Jan till Jun

I want to create KPIs and the users don't want to select / filter out anything

what I mean is that I want to create a line chart showing a line for YTD amount for 2016 vs YTD amount for 2015

for 2016 I created the following expression:

YTD Revenues = var maxYear = max(Master_Calendar[max_Year]) return CALCULATE(sum(view_operations[Amount]),view_account_categorization[Fiscal_Category_DescE]="Budget",view_account_categorization[Fiscal_Chapter_DescE]="Revenues",FILTER(all(Master_Calendar),Master_Calendar[Year]=maxYear && Master_Calendar[Date] <= max(Master_Calendar[Date])))

for 2015 I created the following expression

LYTD Revenues = CALCULATE([YTD Revenues],PREVIOUSYEAR(Master_Calendar[Date]))

However I get the following results which is not logical 

Please advise

[v-sihou-msft]

@alihijazi

If you want to use PREVIOUSYEAR(Master_Calendar[Date]) in this scenario, you have to use a slicer to select current year so that PREVIOUSYEAR() will get correct current year to resolve.

But you mentioned that you don’t want the users to select / filter out anything. So you can try with the formulas provided by @kcantor and @Vvelarde. Both formulas should work here.

Regards,

[kcantor]

@alihijazi

You can also use "DATEADD" for your calculation.

CALCULATE([YTDREVENUES], DATEADD(Master_Calendar[Date],-1, year))

[Vvelarde]

hi; @alihijazi

try with this:

LYTD Revenues = CALCULATE([YTD Revenues],Sameperiodlastyear(Master_Calendar[Date]))

[alihijazi]

This function is not suitable since in 2016 we have data till June so for 2015 will only see data till June
Whereas we want to see for 2015 the whole year

[v-sihou-msft]

@alihijazi

Please try with below formula of “LYTD Revenues”.

LYTD Revenues =
CALCULATE (
    [YTD Revenues],
    PREVIOUSYEAR (
        FILTER (
            VALUES ( Master_Calendar[Date] ),
            YEAR ( Master_Calendar[Date] ) = MAX ( Master_Calendar[max_Year] )
        )
    )
)

[kcantor]

@alihijazi

I would recommend combining a Year-Month column and using two years worth of data instead of a single year. Filter for sales data for 2015 and 2015 in query editor. Add your year-month column to your date table to display as 2015-01, 2015-02 etc. Then use just YTD and use YearMonth on the axis.