The requirement for the pattern is to expose columns related to the week and any aggregation over weeks, such as quarters and years. The Date table used for week-related calculations must include the right definition of all the fiscal periods required – quarter, month, week. Instead, all the formulas use a specific REMOVEFILTERS over the Date table to get rid of the existing filters, replacing them with the minimum number of filters that guarantee the result. The formulas in this pattern do not rely on the automatic REMOVEFILTERS being applied over the Date table when the Date column is filtered. If there is a Date column in the Date table, the Mark as a Date Table setting is allowed but not required. The examples contain one row for each day, in order to create a relationship with the Sales table through the Sales column. The formulas are identical whether you have one row for each week or one row for each day. Therefore, the Date table does not have the requirements needed for standard DAX time intelligence functions. The pattern does not rely on the standard time intelligence functions. The formulas are designed to apply filters at a granularity corresponding to the calculation requirements, without removing filters applied to attributes like working day and day of week this is so that the report granularity is not limited by the implementation of the pattern. The time intelligence calculations in this pattern modify the filter context over the Date table to obtain the result. Introduction to week-related time intelligence calculations The fiscal month and the fiscal quarter always start on the same day of the week, so they do not always start on the first day of a month.The fiscal year always starts on the same day of the week, so it does not always start on January 1.Every period within the year (quarter, month) is a set of complete weeks.
There are many different standards adopted worldwide to define a week-based calendar. You can use the Standard time-related calculations pattern for time-related calculations based on a Gregorian calendar.Įvery time a fiscal calendar is based on weeks, this pattern should be used instead of other patterns based on calendar months. All the measures refer to the fiscal calendar because the nature of a calendar based on weeks is not compatible with the definition of months in a regular Gregorian calendar. This pattern does not rely on DAX built-in time intelligence functions.
It works fine if you're only doing a continous time series view.This pattern describes how to compute week-related calculations, such as year-to-date, same period last year, and percentage growth using a week granularity. The look up method is better able to handle the 53 weeks situation, but then you have a reporting alignment issue, unless you just drop the 53 week for a year-over-year view. This method breaks when you reach a year with 53 weeks. If you do the calculation you just need to pick a year as your starting point and calculate the offset. If this was a supported feature it would make report so much easier, especially when you have to have an adjustment year with 53 weeks. Both have limits, but I could not find any kind of built-in method to set my fiscal calendar within the application so that reports would just work. I had to make my own work around with either a calcuation or a lookup table. I'm including an image of a test I did with just dumping a column of Dates that span across a fiscal year that would start 1/1/17 and go for several period to determine if Spotfire would manage some kind of 13 week aggregation.
Fiscel in Spotfire does not refer to some combination of 13 week quarters (4-5-4,4-4-5,5-4-4). I'm still pretty new to Spotfire, but from what I see the built-in Fiscal functions do not work as expected.