cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarkJames Member
Member

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
MarkJames Member
Member

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.
 
 

View solution in original post

3 REPLIES 3
Community Support Team
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
Community Support Team

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

Hi @MarkJames,

 

Have you solved your problem?

 

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.
MarkJames Member
Member

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.
 
 

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 6 members 1,512 guests
Please welcome our newest community members: