topic Re: SAMEPERIODLASTYEAR with year and month filters in DAX Commands and Tips

SAMEPERIODLASTYEAR with year and month filters

Petsnell — Thu, 18 Mar 2021 14:54:27 GMT

I have a measure for periodic results

'Result = CALCULATE(SUM(Result[sum]),FILTER(ALL(Datotabell),YEAR(Dates[Date])=SELECTEDVALUE(Dates[Year])),

FILTER(ALL(Date),MONTH(Date[Dates])=SELECTEDVALUE(Date[Month])))'

This works just fine, and now I want to create a new table based on 'Result' with values from the same period last year.

'Result last year = CALCULATE(([Result]), SAMEPERIODLASTYEAR(Dates[Date]))'

This returns really large values that I can't really make sense of. Any suggestions?

Re: SAMEPERIODLASTYEAR with year and month filters

amitchandak — Thu, 18 Mar 2021 16:02:04 GMT

Petsnell , do not use the previous result. Ideally if date table is joined then you do not need those filters

if date table is connected

this should be the first measure

Result = CALCULATE(SUM(Result[sum]))

Then result last year will do like

Result last year = CALCULATE(([Result]), SAMEPERIODLASTYEAR(Dates[Date]))'

Result last year = CALCULATE(SUM(Result[sum]),FILTER(all(Result),YEAR(Result[Date])=SELECTEDVALUE(Dates[Year]) -1 && MONTH(Result[Dates])=SELECTEDVALUE(Date[Month])))

If date table is not connected

First measure

Result = CALCULATE(SUM(Result[sum]),FILTER((Result),YEAR(Result[Date])=SELECTEDVALUE(Dates[Year])),

FILTER(ALL(Date),MONTH(Result[Dates])=SELECTEDVALUE(Date[Month])))

second measure

Result = CALCULATE(SUM(Result[sum]),FILTER((Result),YEAR(Result[Date])=SELECTEDVALUE(Dates[Year])),

FILTER(ALL(Date),MONTH(Result[Dates])=SELECTEDVALUE(Date[Month])))

Re: SAMEPERIODLASTYEAR with year and month filters

Petsnell — Fri, 19 Mar 2021 06:49:54 GMT

Result last year = CALCULATE(SUM(Result[sum]),FILTER(all(Result),YEAR(Result[Date])=SELECTEDVALUE(Dates[Year]) -1 && MONTH(Result[Dates])=SELECTEDVALUE(Date[Month])))

This did the trick, thank you so much!