Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous,

 

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.
Anonymous
Not applicable

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.
 
 
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @Anonymous,

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.