Amending DAX to be YTD when current year and FULL when prior years

[tvalente]

Hi All,

I am amending a report built by my predecessor and there's a requirement to have a PY YTD comparison.

This is my attempt below. SAMEPERIODLASTYEAR will only work if I filter the required months in the current non complete year. How do I avoid having to do this behavior?

Avg Personal Leave Taken PY =

VAR MyTable =

ADDCOLUMNS(

GROUPBY(Employees, Employees[EmployeeNo]),

"Leave Days",

CALCULATE(COUNT(Leave[ApplyDate]),

SAMEPERIODLASTYEAR(Dates[Date]),

Leave[Description] in {"PERS WOC", "PERS WC", "PERS SD"}))

RETURN

AVERAGEX(MyTable,

[Leave Days])

[v-xiaoyan-msft]

Hi @tvalente ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thank you very much for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

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

[amitchandak · Accepted solution]

@tvalente , these are examples of Last year data you can get

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

This one is based on today, no need of a date selection
LYTD =
var _min = date(year(today())-1,1,1)
var _max = date(year(today())-1,month(today()),day(today()))
var _day = datediff(_min, _max,day)+1
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year] = year(Today())-1 && 'Date'[Day of Year] <= _max))