cancel
Showing results for
Did you mean:
Frequent Visitor

## SAMEPERIODLASTYEAR for a filter date range based on FISCAL YEAR

I am trying to create a bar chart with sum fact this year versus sum fact for the same period last year, based on a filter date range set by the user.

I was able to get it working using a separate measures table with calculated aggregations, but this meant that the user couldn't filter on any other facts in the data table, without breaking the measures table.

I have taken the calculated measures back into the data table, and am using a contiguous date table. Essentially the table below gives me the data I want for my chart from July 2016 to June 2017; but I can only get it here using the ALLEXCEPT feature, which gives me all periods, not the ones the user has filtered for.

`Total Cost LY = CALCULATE( [Total Cost], SAMEPERIODLASTYEAR('Dates Table'[Calendar Date].[Date]),ALLEXCEPT('Dates Table','Dates Table'[Calendar Date]))`

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

## Re: SAMEPERIODLASTYEAR for a filter date range based on FISCAL YEAR

have you tried to have the ALLSELECTED something like this:

```Total Cost LY =
CALCULATE (
[Total Cost],
SAMEPERIODLASTYEAR ( 'Dates Table'[Calendar Date].[Date] ),
ALLSELECTED ( 'Dates Table'[Calendar Date] )
)```

Or then use the DATEADD function:

`Total Cost LY =CALCULATE (    [Total Cost],    DATEADD ( ALLSELECTED ( 'Dates Table'[Calendar Date] ), -1, YEAR ))`

Regards,

MFelix

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User

## Re: SAMEPERIODLASTYEAR for a filter date range based on FISCAL YEAR

Why don't you try to use this formula:

```Total Cost LY =
CALCULATE (
[Total Cost],
DATEADD ( 'Dates Table'[Calendar Date], -1, Year)
)```

Regards

MFelix

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

4 REPLIES 4
Super User

## Re: SAMEPERIODLASTYEAR for a filter date range based on FISCAL YEAR

have you tried to have the ALLSELECTED something like this:

```Total Cost LY =
CALCULATE (
[Total Cost],
SAMEPERIODLASTYEAR ( 'Dates Table'[Calendar Date].[Date] ),
ALLSELECTED ( 'Dates Table'[Calendar Date] )
)```

Or then use the DATEADD function:

`Total Cost LY =CALCULATE (    [Total Cost],    DATEADD ( ALLSELECTED ( 'Dates Table'[Calendar Date] ), -1, YEAR ))`

Regards,

MFelix

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Frequent Visitor

## Re: SAMEPERIODLASTYEAR for a filter date range based on FISCAL YEAR

Unfortunately when I use the Select function it means if I use any other Filters the chart errors

Super User

## Re: SAMEPERIODLASTYEAR for a filter date range based on FISCAL YEAR

Why don't you try to use this formula:

```Total Cost LY =
CALCULATE (
[Total Cost],
DATEADD ( 'Dates Table'[Calendar Date], -1, Year)
)```

Regards

MFelix

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Highlighted
Frequent Visitor

## Re: SAMEPERIODLASTYEAR for a filter date range based on FISCAL YEAR

will try the add date again, it's clear the issue i was having was contigious dates however, because when I converted the dates to EOMONTH and joined that to a date table with month end dates it worked. Not the most elegant solution but good enough for now.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 46 members 775 guests
Recent signins: