cancel
Showing results for
Did you mean:
Frequent Visitor

SAMEPERIODLASTYEAR with year and month filters

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?

1 ACCEPTED SOLUTION
Super User IV

@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]))'

or

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])))

Proud to be a Super User!

2 REPLIES 2
Super User IV

@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]))'

or

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])))

Proud to be a Super User!

Frequent Visitor

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!

Announcements