We all know the usual Time Intelligence function based on years, quarters, months, and days. But\u00a0sometimes, we need to perform more exotic timer intelligence calculations. But we should not forget to consider performance while programming the measures.\u00a0<\/p>\n
Introduction\u00a0<\/h2>\n
There are many Dax<\/a> functions in Power BI for Time Intelligence Measures.\u00a0<\/p>\n The most common are:\u00a0<\/p>\n You can find a comprehensive list of Time Intelligence functions here: Time Intelligence \u2013 DAX Guide<\/a>. These functions cover the most common cases.\u00a0<\/p>\n However, some requirements cannot be easily covered with these functions. And here we are.\u00a0<\/p>\n I want to cover some of these cases I encountered in my projects, which include:\u00a0<\/p>\n I will show you how to use an extended date table to support these scenarios and improve efficiency and performance.\u00a0<\/p>\n Most Time-Intelligence functions work regardless of whether the Fiscal Year is aligned with the calendar year. One exception is Year-to-Date (YTD).\u00a0<\/p>\n For such cases, look at the DATESYTD() function mentioned above. There, you will find the optional parameter to pass the last day of the Fiscal year.\u00a0<\/p>\n The last case will cover calculations based on weeks, while the Fiscal year doesn\u2019t align with the calendar year.\u00a0<\/p>\n I will use the well-known ContosoRetailDW data model.<\/p>\n The Base Measure is Sum Online Sales, which has the following code:\u00a0<\/p>\n I will work almost exclusively in DAX-Studio<\/a>, which provides the Server Timing function to analyze the performance of the DAX code. In the References section below, you can find a link to an article about how to collect and interpret performance data in DAX Studio.\u00a0<\/p>\n This is the base query used in my examples to get some data from the data model:\u00a0<\/p>\n In most examples, I will remove some filters to get more complete data (for each day).\u00a0<\/p>\n My date table includes a relatively large number of additional columns.\u00a0<\/p>\n In the references section below, you can find some articles written by SQLBI, on building weekly related calculations, including creating a date table to support these calculations.\u00a0<\/p>\n As described in my article about date tables referenced below, I have added the following columns:\u00a0<\/p>\n As you will see, I will use these columns extensively to simplify my calculations.<\/p>\n In addition, we will use the Calendar Hierarchy to calculate the needed results at different levels of\u00a0the hierarchy.\u00a0<\/p>\n A complete Calendar hierarchy contains either:\u00a0<\/p>\n Or\u00a0<\/p>\n If the Fiscal Year doesn\u2019t align with the Calendar year, I built the Hierarchy with the Fiscal Year instead of the Calendar Year.\u00a0<\/p>\n Then, I added a separate FiscalMonthName column and a FiscalMonthSort column to ensure that the first month of the fiscal year was shown first.\u00a0<\/p>\n OK, let\u2019s start with the first case.\u00a0<\/p>\n This scenario calculates the rolling sum of values over the past n periods.\u00a0<\/p>\n For example, for each day, we want to get the Sales for the last 10 days:\u00a0<\/p>\n Here is the Measure I came up with:\u00a0<\/p>\n When executing the query filtering for Computers and North America, I get this result: <\/p>\n If I look at the server timings, the result is not bad:\u00a0<\/p>\n As you can see, the Storage engine performs more than half of the work, which is a good sign. It\u2019s not\u00a0perfect, but as the execution time is less than 100 ms, it\u2019s still very good from the performance point of view.\u00a0<\/p>\n This approach has one crucial issue:<\/p>\n When calculating the rolling sum over multiple months, you must know that this approach is date oriented.\u00a0<\/p>\n This means that when you look at a specific time, it goes back to the same day of the given month. For example:\u00a0<\/p>\n We look at January 12. 2024, and we want to calculate the rolling sum over the last three months. The starting date for this calculation will be November 13. 2023.\u00a0<\/p>\n When do we want to get the rolling sum for the entire month?\u00a0<\/p>\n In the case above, I want to have as the starting date November 1, 2023.\u00a0<\/p>\n For this case, we can use the MonthIndex column.\u00a0<\/p>\n Each column has a unique index based on the current date.\u00a0<\/p>\n Therefore, we can use it to go back three months and get the entire month.\u00a0<\/p>\n This is the DAX Code for this:\u00a0<\/p>\n The execution is still quick, but it\u2019s less efficient, as most of the calculations cannot be performed by the Storage engine:<\/p>\n I tried other approaches (for example, Here is the result for the same logic, but for the last two months (To avoid showing too many rows):<\/p>\n The leap year problem is odd, which is evident when calculating the previous year for each day. Let me explain:\u00a0<\/p>\n When I execute the following Query to get the last days of February for the years 2020 and 2021:\u00a0<\/p>\n I get the following result:\u00a0<\/p>\n As you can see above, the result for February 28. 2020 is shown twice, and one day is missing the February 2021 for Online Sales (PY).\u00a0<\/p>\n When looking at the month, the sum is correct:\u00a0<\/p>\n The problem is that there is no February 29 in 2021. Therefore, there is no way that the sales for\u00a0February 29, 2020 will be displayed when listing the Sales Amount per day.\u00a0<\/p>\n While the result is correct, it will be wrong when the data is exported to Excel, and the values are\u00a0summed. Then, the sum of the daily results will differ from those shown for the entire month.\u00a0<\/p>\n This can undermine the users\u2019 perceived reliability of the data.\u00a0<\/p>\n My solution was to add a Then, I added a calculated column for each month and day ( The Measure to calculate the previous year manually and using the new table is the following:<\/p>\n As you can see, I got the current year, and by using the VALUES() function<\/a>, I got the list of all dates in the current filter context.\u00a0<\/p>\n Using this method, my Measure works for single Days, Months, Quarters, and Years. The result of this Measure is the following:\u00a0<\/p>\n As you can see here, the Measure is very efficient, as most of the work is done by the Storage engine: <\/p>\n But, to be honest, I don\u2019t like this approach, even though it works very well.\u00a0<\/p>\n The reason is that the LeapYearDate table does not have a date column. Therefore, it cannot be used as a Date table for the existing Time Intelligence functions.\u00a0<\/p>\n We must also use the calendar columns from this table in the visualizations. We cannot use the ordinary date table.\u00a0<\/p>\n Consequently, we must reinvent all Time Intelligence functions to use this table.<\/p>\n I strongly recommend using this approach only when necessary.\u00a0<\/p>\n Some Business areas concentrate on Weekly analysis.\u00a0<\/p>\n Unfortunately, the standard Time Intelligence functions do not support weekly analysis out of the box. Therefore, we must build our Weekly Measures by ourselves.\u00a0<\/p>\n The first Measure is WTD.\u00a0<\/p>\n The first approach is the following:\u00a0<\/p>\n As you can see, I use the When you adapt this pattern to your situation, you must ensure that the second parameter in\u00a0 The result is the following:<\/p>\n Another approach is to store the first date of each week in the Date table and use this information in the Measure:\u00a0<\/p>\n The result is precisely the same.\u00a0<\/p>\n When analyzing the performance in DAX Studio, I see that both Measures are comparable to each other:<\/p>\n \u00a0<\/em><\/p>\n I tend to use the second one, as it has better potential when combined with other Measures. But in the end, it depends on the current scenario.\u00a0<\/p>\n Another challenge is to calculate the previous year.\u00a0<\/p>\n Look at the following dates for the same week in different weeks:\u00a0<\/p>\n As you can see, the dates are shifted. And as the standard time intelligence functions are based on\u00a0shifting dates, they will not work.\u00a0<\/p>\n I tried different approaches, but in the end, I stored the first date of the same week for the previous year in the date table and used it like in the second version of WTD shown above:\u00a0<\/p>\n This is the result:\u00a0<\/p>\n As the logic is the same as in the WTD v2, the performance is also the same. Therefore, this Measure is very efficient.\u00a0<\/p>\n Sometimes, the weekly view is enough, and we don\u2019t need to calculate the WTD at the Daily level.\u00a0<\/p>\n We don\u2019t need a WTD Measure for this scenario for the current year. The base Measure sliced by Week can cover this. The result is correct out of the box.\u00a0<\/p>\n But, again, it\u2019s another story for PY.<\/p>\n This is the first version I came up with:\u00a0<\/p>\n Here, I subtract one from the current year while retaining the filter for the current week. This is the result:<\/p>\n The performance is good, but I can do better.\u00a0<\/p>\n What if I could store a unique Week Identifier in the Date column?\u00a0<\/p>\n For example, the Current Week is 9 of 2025..\u00a0<\/p>\n The Identifier would be 202509.\u00a0<\/p>\n When I detract 100 from it, I get 202409, the identifier for the same week in the previous year. After adding this column to the date table, I can change the Measure to this:\u00a0<\/p>\n This version is much simpler than before, and the result is still the same.\u00a0<\/p>\n When we compare the execution statistics of the two versions, we see this:\u00a0<\/p>\n As you can see, the second version, with the precalculated column in the Date table, is slightly more efficient. I have only four SE queries, a good sign for increased efficiency.\u00a0<\/p>\n This last one is tricky.\u00a0<\/p>\n The requirement is that the user wants to see a YTD starting from the first day of the first week of the Fiscal year.\u00a0<\/p>\n For example, the Fiscal year starts on July 1.\u00a0<\/p>\n In 2022, the week containing July the 1st <\/sup>starts on Monday, June 27.\u00a0<\/p>\n This means that the YTD calculation must start on this date.\u00a0<\/p>\n The same applies to the YTD PY calculation starting Monday, June 28, 2021.\u00a0<\/p>\n This approach has some consequences when visualizing the data.\u00a0<\/p>\n Again, knowing if the result must be shown at the day or week level is essential. When showing the data at the day level, the result can be confusing when selecting a Fiscal Year:<\/p>\n As you can see, Friday is the first day of the Fiscal year. And the YTD result doesn\u2019t start on July 1st <\/sup>but on Monday of that week.\u00a0<\/p>\n The consequence is that the YTD doesn\u2019t seem to start correctly. The users must know what they are looking at.\u00a0<\/p>\n The same is valid for the YTD PY results.\u00a0<\/p>\n To facilitate the calculations, I added more columns to the Date table:\u00a0<\/p>\n Here is the Measure for the Daily YTD:<\/p>\n Here is the DAX Code for the Daily YTD PY:<\/p>\n As you can see, both Measures follow the same pattern:\u00a0<\/p>\n For the PY Measure, one additional step is required:\u00a0<\/p>\n And here is the DAX code for the weekly base YTD:\u00a0<\/p>\n For the weekly YTD PY, the DAX code is the following:\u00a0<\/p>\n Again, both Measures follow the same pattern:\u00a0<\/p>\n The result for the weekly based Measure is the following (At the weekly level, as the value is the\u00a0same for each day of the same week):\u00a0<\/p>\n When comparing the two Approaches, the Measure for the weekly calculation is more efficient than the one for the daily calculation:<\/p>\n As you can see, the Measure for the weekly result is faster, has a more significant portion executed in the Storage Engine (SE), and has fewer SE queries.\u00a0<\/p>\n Therefore, it can be a good idea to ask the users if they need a WTD result at the day level or if it\u2019s enough to see the results at the week level.\u00a0<\/p>\n When you start writing Time Intelligence expressions, consider whether additional calculated columns in your date table can be helpful.\u00a0<\/p>\n A carefully crafted and extended date table can be helpful for two reasons:\u00a0<\/p>\n They will be easier to write as I do not need to perform the calculations to get the intermediary results to calculate the required results.\u00a0<\/p>\n The consequence of shorter and simpler Measures is better efficiency and performance.\u00a0<\/p>\n I will add more and more columns to the template of my date table as I encounter more situations in which they can be helpful.\u00a0<\/p>\n One question remains: How to build it?\u00a0<\/p>\n In my case, I used an Azure SQL database to create the table used in my examples.\u00a0<\/p>\n But it\u2019s possible to create a date table as a DAX table or use Python or JavaScript in Fabric or whatever data platform you use.\u00a0<\/p>\n Another option is to use the Bravo tool from SQLBI, which allows you to create a DAX table containing additional columns to support exotic Time Intelligence scenarios.\u00a0<\/p>\n You can find more information about my date-table here<\/a>.\u00a0<\/p>\n Read this piece<\/a> to learn how to extract performance data in DAX-Studio and how to interpret it.\u00a0<\/p>\n An SQLBI article<\/a> about building a date table to support weekly calculations: Using weekly calendars in Power Bi<\/a> \u2013 SQLBI\u00a0<\/p>\n SQLBI Pattern to perform further weekly calculations:\u00a0<\/p>\n Week-related calculations \u2013 DAX Patterns<\/a>\u00a0<\/p>\n Like in my previous articles, I use the Contoso sample dataset. You can download the\u00a0ContosoRetailDW Dataset for free from Microsoft here<\/a>.\u00a0<\/p>\n The Contoso Data can be freely used under the MIT License, as described here<\/a>. <\/p>\n I changed the dataset to shift the data to contemporary dates.\u00a0<\/p>\n The post Advanced Time Intelligence in DAX with Performance in Mind<\/a> appeared first on Towards Data Science<\/a>.<\/p>\n<\/div>\n\n
<a href=\"https:\/\/dax.guide\/dateadd\/\">DATEADD()<\/a>\u00a0<\/code><\/li>\n\n
Scenario\u00a0<\/h2>\n
Sum Online Sales = SUMX('Online Sales',\n\u00a0( 'Online Sales'[UnitPrice]\n\u00a0\u00a0 * 'Online Sales'[SalesQuantity] )\u00a0\n - 'Online Sales'[DiscountAmount] )<\/code><\/pre>\nEVALUATE \n CALCULATETABLE( \n SUMMARIZECOLUMNS('Date'[Year] \n ,'Date'[Month Short Name] \n,'Date'[Week] \n,'Date'[Date] \n,\"Online Sales\", [Sum Online Sales] \n) \n ,'Product'[ProductCategoryName] = \"Computers\" ,'Product'[ProductSubcategoryName] = \"Laptops\" \n,'Customer'[Continent] = \"North America\" \n ,'Customer'[Country] = \"United States\" ,'Customer'[State\/Province] = \"Texas\" )\n<\/code><\/pre>\nDate table\u00a0<\/h2>\n
\n
\n
\n
Last n periods\u00a0<\/h2>\n
![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/Screenshot-2025-02-19-at-11.40.18%25E2%2580%25AFAM-1024x634.png?resize=1024%2C634&ssl=1\")
Online Sales (Last 10 days) = \n CALCULATE (\n [Sum Online Sales] \n ,DATESINPERIOD ( \n 'Date'[Date], \nMAX ( 'Date'[Date] ), \n-10, \nDAY \n ) \n ) <\/code><\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/Screenshot-2025-02-19-at-11.42.19%25E2%2580%25AFAM.png?resize=540%2C348&ssl=1\")
Online Sales rolling full 3 months = \n VAR CurDate = \n MAX ( 'Date'[Date] ) \n VAR CurMonthIndex = \n MAX ( 'Date'[MonthIndex] ) \n VAR FirstDatePrevMonth = \n CALCULATE ( \n MIN ( 'Date'[Date] ), \n REMOVEFILTERS ( 'Date' ), \n 'Date'[MonthIndex] = CurMonthIndex - 2 \n ) \n RETURN \n CALCULATE ( \n [Sum Online Sales], \n DATESBETWEEN ( \n 'Date'[Date], \nFirstDatePrevMonth, \nCurDate \n ) \n )<\/code><\/pre>\n'Date'[MonthIndex] >= CurMonthIndex \u2013 2 &&<\/code> 'Date'[MonthIndex] <= CurMonthIndex)<\/code>, but these approaches were worse than this one.\u00a0<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/Screenshot-2025-02-19-at-11.45.25%25E2%2580%25AFAM.png?resize=605%2C672&ssl=1\")
Regarding Leap Years\u00a0<\/h2>\n
EVALUATE \nCALCULATETABLE ( \n SUMMARIZECOLUMNS ( \n 'Date'[Year], \n 'Date'[Month Short Name], \n 'Date'[MonthKey],\n 'Date'[Day Of Month], \n \"Online Sales\", [Sum Online Sales], \n \"Online Sales (PY)\", [Online Sales (PY)] \n ), \n 'Date'[Year] IN {2020, 2021}, \n 'Date'[Month] = 2, \n 'Date'[Day Of Month] IN {27, 28, 29}, \n 'Customer'[Continent] = \"North America\", \n 'Customer'[Country] = \"United States\" \n) \n ORDER BY 'Date'[MonthKey], \n 'Date'[Day Of Month]<\/code><\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/Screenshot-2025-02-19-at-11.47.10%25E2%2580%25AFAM.png?resize=547%2C159&ssl=1\")
LeapYearDate<\/code> table. This table is a copy of the Date table but without a\u00a0Date column. I added one row each year on February 29, even for non-leap years.\u00a0<\/p>\nMonthDay<\/code>):\u00a0<\/p>\nMonthDay = ('LeapYearDate'[Month] * 100 ) + 'LeapYearDate'[Day Of Month]<\/code><\/pre>\nOnline Sales (PY Leap Year) = \n VAR ActYear = \n SELECTEDVALUE ( 'LeapYearDate'[Year] ) \n VAR ActDays = \n VALUES ( 'LeapYearDate'[MonthDay] ) \n RETURN \n CALCULATE ( \n [Sum Online Sales], \n REMOVEFILTERS ( LeapYearDate ), \n 'LeapYearDate'[Year] = ActYear - 1, \n ActDays \n )<\/code><\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/Screenshot-2025-02-19-at-11.56.10%25E2%2580%25AFAM.png?resize=598%2C183&ssl=1\")
![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/Screenshot-2025-02-19-at-11.57.07%25E2%2580%25AFAM.png?resize=125%2C182&ssl=1\")
Week to Date and PY\u00a0<\/h2>\n
Online Sales WTD v1 = \n VAR MaxDate = MAX('Date'[Date]) \n \n VAR CurWeekday = WEEKDAY(MaxDate, 2) \n \n RETURN \n CALCULATE([Sum Online Sales] \n ,DATESBETWEEN('Date'[Date] \n ,MaxDate - CurWeekDay + 1 ,MaxDate) \n )<\/code><\/pre>\nWEEKDAY()<\/code> function<\/a> to calculate the start date of the week. Then, I use the DATESBETWEEN()<\/code> function<\/a> to calculate the WTD.\u00a0<\/p>\nWEEKDAY()<\/code> is set to the correct value. Please read the documentation to learn more about it.\u00a0<\/p>\n![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/Screenshot-2025-02-19-at-11.59.03%25E2%2580%25AFAM.png?resize=462%2C450&ssl=1\")
Online Sales WTD PY v2 = \n VAR DayOfWeek = MAX('Date'[Day Of Week]) \n \n VAR FirstDayOfWeek = MIN('Date'[FirstDayOfWeekDatePY]) \n RETURN \n CALCULATE([Sum Online Sales] \n ,DATESBETWEEN('Date'[Date] \n ,FirstDayOfWeek \n,FirstDayOfWeek + DayOfWeek - 1) \n )\n<\/code><\/pre>\nOnline Sales WTD PY = \n VAR DayOfWeek = MAX('Date'[Day Of Week]) \n \n VAR FirstDayOfWeek = MIN('Date'[FirstDayOfWeekDatePY]) \n RETURN \n CALCULATE([Sum Online Sales] \n ,DATESBETWEEN('Date'[Date]\n ,FirstDayOfWeek \n,FirstDayOfWeek + DayOfWeek - 1) \n )\n<\/code><\/pre>\n![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/Screenshot-2025-02-19-at-12.01.29%25E2%2580%25AFPM.png?resize=594%2C577&ssl=1\")
Weekly Sums for PY\u00a0<\/h2>\n
Online Sales (PY Weekly) v1] = \n VAR ActYear = MAX('Date'[Year]) \n \n RETURN \n CALCULATE([Sum Online Sales] \n ,ALLEXCEPT('Date' \n ,'Date'[Week] \n) \n ,'Date'[Year] = ActYear - 1 \n )\n<\/code><\/pre>\nMEASURE 'All Measures'[Online Sales (PY Weekly) v2] = \nVAR WeeksPY = VALUES('Date'[WeekKeyPY]) \nRETURN \nCALCULATE([Sum Online Sales]\n,REMOVEFILTERS('Date') \n,'Date'[WeekKey] IN WeeksPY \n)\n<\/code><\/pre>\nFiscal Weeks YTD\u00a0<\/h2>\n
![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/Screenshot-2025-02-19-at-12.04.25%25E2%2580%25AFPM-1024x348.png?resize=1024%2C348&ssl=1\")
\n
Online Sales (Fiscal Week YTD) =\nVAR FiscalYearWeekYear = MAX('Date'[FiscalYearWeekYear])\nVAR StartFiscalYear = CALCULATE(MIN('Date'[Date])\n,REMOVEFILTERS('Date')\n,'Date'[FiscalYearWeekSort] =\n\nFiscalYearWeekYear * 100 + 1\n\n)\n\nVAR FiscalYearStartWeekDate = CALCULATE(MIN('Date'[FirstDayOfWeekDate])\n,ALLEXCEPT('Date'\n,'Date'[FiscalYearWeekYear]\n)\n,'Date'[Date] = StartFiscalYear\n\n)\nVAR MaxDate = MAX('Date'[Date])\nRETURN\nCALCULATE([Sum Online Sales]\n,REMOVEFILTERS('Date')\n\n,DATESBETWEEN('Date'[Date]\n,FiscalYearStartWeekDate\n\n,MaxDate\n)<\/code><\/pre>\nOnline Sales (Fiscal Week YTD) (PY)] =\nVAR FiscalYearWeekYear = MAX('Date'[FiscalYearWeekYear])\n-- Get the Week\/Weekday at the start of the current Fiscal Year\nVAR FiscalYearStart = CALCULATE(MIN('Date'[Date])\n,REMOVEFILTERS('Date')\n,'Date'[FiscalYearWeekSort] =\n\nFiscalYearWeekYear * 100 + 1\n)\nVAR MaxDate = MAX('Date'[Date])\n-- Get the number of Days since the start of the FiscalYear\nVAR DaysFromFiscalYearStart =\nDATEDIFF( FiscalYearStart, MaxDate, DAY )\n-- Get the PY Date of the Fiscal Year Week Start date\nVAR DateWeekStartPY = CALCULATE(MIN('Date'[Date])\n,REMOVEFILTERS('Date')\n,'Date'[FiscalYearWeekSort] =\n\n(FiscalYearWeekYear - 101) * 100 + 1\n)\nRETURN\nCALCULATE(\n[Sum Online Sales],\nDATESBETWEEN(\n'Date'[Date],\nDateWeekStartPY,\nDateWeekStartPY + DaysFromFiscalYearStart\n\n)\n)<\/code><\/pre>\n\n
\n
Online Sales (Fiscal Week YTD) =\nVAR FiscalWeekSort = MAX( 'Date'[FiscalWeekSort] )\n-- Get the Week\/Weekday at the start of the current Fiscal Year\nVAR FiscalYearNumber = MAX( 'Date'[FiscalYearWeekYear] )\n\nRETURN\nCALCULATE(\n[Sum Online Sales],\nREMOVEFILTERS('Date'),\n'Date'[FiscalYearWeekSort] >= (FiscalYearNumber * 100 ) + 1\n&& 'Date'[FiscalYearWeekSort] <= (FiscalYearNumber * 100 ) +\nFiscalWeekSort\n)<\/code><\/pre>\nOnline Sales (Fiscal Week YTD) (PY) =\nVAR FiscalWeekSort = MAX( 'Date'[FiscalWeekSort] )\n-- Get the Week\/Weekday at the start of the current Fiscal Year\nVAR FiscalYearNumberPY = MAX( 'Date'[FiscalYearWeekYearPY] )\nRETURN\nCALCULATE(\n[Sum Online Sales],\nREMOVEFILTERS('Date'),\n'Date'[FiscalYearWeekSort] >= (FiscalYearNumberPY * 100) + 1\n&& 'Date'[FiscalYearWeekSort] <= (FiscalYearNumberPY * 100) +\nFiscalWeekSort\n)<\/code><\/pre>\n\n
![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/Screenshot-2025-02-19-at-12.11.56%25E2%2580%25AFPM-1024x361.png?resize=1024%2C361&ssl=1\")
![\"\"](\"https:\/\/i0.wp.com\/towardsdatascience.com\/wp-content\/uploads\/2025\/02\/Screenshot-2025-02-19-at-12.12.32%25E2%2580%25AFPM.png?resize=524%2C368&ssl=1\")
Conclusion\u00a0<\/h2>\n
\n
References\u00a0<\/h2>\n
![\"\"](\"https:\/\/lh7-rt.googleusercontent.com\/docsz\/AD_4nXcibj9TX2MPwXZkLifW2GA9Pc_0sXdZoF-xKlcebbNaQoooERUFe16UI9PaybXgV_iqsgqUytvK1jQ-ski9o8RxpXNM02ZEEC285aXSQzccGL_cjji3S57QJsuaCIjwNVQKgu4PwQ?key=UZ99HMJ_TyEY5euXsrcZKGZ7\")
![\"\"](\"https:\/\/lh7-rt.googleusercontent.com\/docsz\/AD_4nXfBlmh8jKSd02mFprWGtUyZWhKDVf4LdALF_SUcghTwSrt9osiarB5xsrALXMZoUK3bNKH_Nez7fwpjAvJvzx1H7lZdwzeq7gdrGzVKRcI_KBy2TUAVnUD_9ziZ7JYQr-1N4thmSg?key=UZ99HMJ_TyEY5euXsrcZKGZ7\")
![\"\"](\"https:\/\/lh7-rt.googleusercontent.com\/docsz\/AD_4nXeMRTAAF7ZFO5t0diNEWqq21NjG9F0RJx-d_61EJ51Ad6oXkDon0rftxZFJDmHU5oaozW8MKU9HrbmB81WOvNLEzcpIoFspY6R82ZJl97z40-rlCSTrvXgLCBmTyT1KlqFoFpEEDQ?key=UZ99HMJ_TyEY5euXsrcZKGZ7\")
![\"\"](\"https:\/\/lh7-rt.googleusercontent.com\/docsz\/AD_4nXc4fqdyEN-XX760lAz4f7IG7A_vzJ0goGTg3UJ1ROmDi8eSlwwN8Yo7stKebfKlrd-O6ByNHBIx0c5-PVlbrtou3I8gOHRd498eekPJCzRC6X_zDzhYuDuYqFG17sGFUrpqG4SwPg?key=UZ99HMJ_TyEY5euXsrcZKGZ7\")
![\"\"](\"https:\/\/lh7-rt.googleusercontent.com\/docsz\/AD_4nXdf6SsT728scuGCqhXnVacrNc8VivcjR5iA3XW5c23KibM16qiN3ptNwGM8PVEMBvVyj_bJ6GeIsgBg84TEMdGwmVMygPtSiygH6MI7f1VXSMHDi1MmUFvGGgzGDxMmHaGG6aIIIw?key=UZ99HMJ_TyEY5euXsrcZKGZ7\")
![\"\"](\"https:\/\/lh7-rt.googleusercontent.com\/docsz\/AD_4nXdHdrAi-ZEW9_C4LDBMvzbGF5TxilbNf-vZk--bLPwl8ge9weDeNYVMxp-f--3d41zrRdwrDtTDjeWanG-US0XUDk6KUr9RlmFRLirJfgo9XcNhjMBtVxuYAnGnf6C-Rly6gFvr?key=UZ99HMJ_TyEY5euXsrcZKGZ7\")
![\"\"](\"https:\/\/lh7-rt.googleusercontent.com\/docsz\/AD_4nXeQqUUspkTxfAcQTjDXqLVIQnbLrrgrPLwcvPQnWrvqvDW0r-ut3gx3JDhg4yJOezvbUr-QFlLvQL717hGXE6m-L85QrY0_T9eY6jQMdmWlPu_xre5S2LezJem8b1qfOuMYfoopnQ?key=UZ99HMJ_TyEY5euXsrcZKGZ7\")