How to calculate Totals when using Custom Calendars (your video)

Hi there,
I watched your tutorial on Managing Custom Calendars - Time Comparison and found it very helpful because my company uses a 445 fiscal calendar and I’ve always struggled with DATEADD, SAMEPERIODLASTYEAR, etc.

I applied your measure to my work and got the desired results, however, it doesn’t give me a total. So I went back to your tutorial and your example doesn’t show totals either.

How can I get the column to total if/when I apply a filter. I added a Month filter to your example so you can see what I mean.

Also, I noticed that if you remove “Financial Week Number” from the table, you lose everything from the Custom column. I understand that it has to be there because it’s part of the measure, but what if the user needs to filter by Financial Week Number and not have it be in the table itself? This example shows my problem. I need to show JUST Sales TY and Sales LY without the Year and Week # in the table itself. My users want to filter by those things. Here you can see that the Custom Sales for LY is blank.


Hi @Rose

When you are using custom calendar, you can’t use SAMEPERIODLASTYEAR, you’ve to write your own code.
Please find below sample code for Custom calendar, you can change the code based on your requirement.

Let me know if you need any further help.

1 Like

For your second requirement where you don’t want to show the Week # & Year in the table, you can use some tricks to hide them. First you have to go to Format tab and then Go to Values and turn off the Word Wrap.

Then Under Format tab, go to Style and select None

Now again under Format Tab, go to Field Formatting and select the column you want to hide. In this case we will be hiding Year & Month #. Select Year under Field Formatting and make font color the same as your background color


However, the header and total is till there. So again under Field Formatting Turn on the options Apply to header & Apply to total

Next step is to collapse the column size & the column will be hidden:

As you can see that the Word Wrap is on in the Header which increasing the size of the header when collapsing a column. So again under Format Tab go to Column Headers and Turn Off Word Wrap!

Now repeat the same steps for Month # or Week # in your case.


Hi @Rose, did the response provided by the contributors help you solve your query? If not, how far did you get, and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

I already have my own custom calendar that is arranged by the 445 financial calendar.


I shared the logic to get Last year sales for Custom Calendar.

Hi @Rose, we’ve noticed that no response has been received from you since the 10th of November. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!