cancel
Showing results for
Did you mean:
Regular Visitor

## Cumulative sum by either Calendar Year of Fiscal Year versus same period last year

Hi,

I have rather a complex problem I'm hoping someone can help me make simple ...

I have a calendar table that will either run by Calendar Year of Fiscal Year (July to June) depending on a parameter that switchs between CY and FY - so there will only ever be one version of the table, the parameter will adjust the start date and calendar type.

I want to be able compare the cumulative sum of a field YEAR TO DATE, regardless of whether it's a CY or FY calendar:

• If I have a CY calendar I'll be comparing 01/01/2018 to 06/11/2018 against 01/01/2017 to 06/11/2017
• If I have a FY calendar the measure will compare 01/07/2018 to 06/11/2018 against 01/07/2017 to 06/11/2017

My calendar table had all the usual fields - date, month, year, etc. Obviously when the dates go from 30/06/2018 to 01/07/2018 to years change from 2018 to 2019.

Am I right in thinking SAMEPERIODLASTYEAR only works on Calendar Year and not Fiscal Year?

I really don't know how I should approach this. Does anyone have any ideas?

Thanks,

MarkJames

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Regular Visitor

## Re: Cumulative sum by either Calendar Year of Fiscal Year versus same period last year

No problem. I'm sure there's a smarter way to do this but it's working and that's all I wanted.

I created a calculated column that returned the MAX of the YEAR column - so I could work out the current year and that it would be dynamic as the data moved through the years:

Max Year = CALCULATE(MAX('Calendar'[Year]),FILTER('Calendar','Calendar'[Calendar Type]=EARLIER('Calendar'[Calendar Type])))

I then created another column that checked if YEAR matched MAX YEAR to identify the CURRENT YEAR:

Current Year = if('Calendar'[Year]='Calendar'[Max Year],"Current Year",'Calendar'[Year as Text])

I'd created a YEAR AS TEXT column to return but I could have used FORMAT to convert the column to text within the CURRENT YEAR column.

I then used CURRENT YEAR in a slicer and selected CURRENT YEAR, which will always show the most recent year. I then added a DATE RANGE filter and SAMEPERIODLASTYEAR to work out the comparison I wanted.

So now when I change from CY to FY and the MAX YEAR changes I still get the right values from the calculations.

Hopefully that makes sense.

3 REPLIES 3
Community Support Team

## Re: Cumulative sum by either Calendar Year of Fiscal Year versus same period last year

Hi @MarkJames,

For your scenario, you'd better not use SAMEPERIODLASTYEAR fucntions.

You could have a reference of this similar thread which has been solved.

If you still need help, please share some data sample which could reproduce your scenario and your desired output so that we could help further on it.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team

## Re: Cumulative sum by either Calendar Year of Fiscal Year versus same period last year

Hi @MarkJames,

If you have solved, could you please share your solution or always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please share some data sample and your desired output so that we could help further on it.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Regular Visitor

## Re: Cumulative sum by either Calendar Year of Fiscal Year versus same period last year

No problem. I'm sure there's a smarter way to do this but it's working and that's all I wanted.

I created a calculated column that returned the MAX of the YEAR column - so I could work out the current year and that it would be dynamic as the data moved through the years:

Max Year = CALCULATE(MAX('Calendar'[Year]),FILTER('Calendar','Calendar'[Calendar Type]=EARLIER('Calendar'[Calendar Type])))

I then created another column that checked if YEAR matched MAX YEAR to identify the CURRENT YEAR:

Current Year = if('Calendar'[Year]='Calendar'[Max Year],"Current Year",'Calendar'[Year as Text])

I'd created a YEAR AS TEXT column to return but I could have used FORMAT to convert the column to text within the CURRENT YEAR column.

I then used CURRENT YEAR in a slicer and selected CURRENT YEAR, which will always show the most recent year. I then added a DATE RANGE filter and SAMEPERIODLASTYEAR to work out the comparison I wanted.

So now when I change from CY to FY and the MAX YEAR changes I still get the right values from the calculations.

Hopefully that makes sense.