Sameperiodlastyear expects a contiguous selection. The set-up is now as follows: Totalsalg YTD = TOTALYTD ( [Totalsalg];DATESYTD (Dates [Date])) Totalsalg LYTD = CALCULATE ( [Totalsalg YTD];SAMEPERIODLASTYEAR ('Dates' [Date])) And I needed something to filter the year. This is becuase the fact table date variation issue date produces the error, Function 'SAMEPERIODLASTYEAR' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion. I have a DateTable and SalesTable. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option. Apr 27, 2020 · Hi @StephenK ,. Next, we create another measure, YOY% Changes, as shown below: DATEADD "expects a contiguous selection when the date column is not unique, has gaps" Function 'SAMEPERIODLASTYEAR' expects a contiguous selection Issue . May 28, 2019 · DATEADD "expects a contiguous selection when the date column is not unique, has gaps" 05-26-2019 04:23 AM. in/learn-power-query/ 'Trandata'[TotalIncomeLastYear]: Function "SAMEPERIODLASTYEAR" expects a contiguous selection when the date column is not unique, has gaps or it contains time portion. Best Regards, Dale Apr 20, 2017 · I had this issue and changing the sameperiodlastyear from my year-month column to the full date column as suggested solved the issue, thanks! Message 16 of 17 4,335 Views May 28, 2019 · OK, now I've created a Dates Table and related it for the "Order Date" on the "Sales" table. Hola a todos, Tengo la siguiente medida: Capacidad dinámica SPLY - CALCULATE (SUM ('Fact_Schedule' [DynamicAdjustedSlots]),SAMEPERIODLASTYEAR ('Date Dim' [Date]))- [DynamicPTO]Estoy recibiendo el siguiente mensaje de error: Mi dim de fecha es una tabla de calendario y tiene Aug 2, 2018 · Totalsalg LYTD = CALCULATE([Totalsalg YTD];SAMEPERIODLASTYEAR('Dates'[Date])) And I needed something to filter the year. It is specific to a year interval and returns the same as the dates returned by this equivalent expression: DATEADD (dates, -1, year). Hi @Rony21 , You can refer the following links to try to solve your problem: DATEADD "expects a contiguous selection when the date column is not unique, has gaps" Function 'SAMEPERIODLASTYEAR' expects a contiguous selection Issue. Nov 24, 2016 · 1 Answer. The problem can come if, for example, the slicer shows 1/31/2021 but the last date in FirstRegistrationDate is - 1/15/2021 so you will get offset for a 1/15/2021 not for 1/31/2021. Varous google searches seem to always suggest making sure all dates are there and a 1 to many relationship, which I do have. Nov 5, 2019 · In the result, it shows that the SAMEPERIODLASTYEAR function returns the same date last year with the filter context in month level. Mar 25, 2024 · The syntax for SAMEPERIODLASTYEAR is straightforward: SAMEPERIODLASTYEAR(<dates>) However, it's essential to note that the <dates> argument expects a column of dates that are contiguous; this means there should be no gaps between the dates. guide/dateadd/. Message 2 of 2 107 Views Power BI 101 Interview questions!! !!Master Microsoft Fabric- 36 Videos!! Microsoft Power BI Learning Resources, 2023 !! Learn Power BI - Full Course with Dec-2022, with Window, Index, Offset, 100+ Topics !! Dec 12, 2018 · It works if my relationship between date table and FACT table are both ways but I get "Function 'SAMEPERIODLASTYEAR' expects a contiguous selection" if I filter on a specific shop. Here is a simplified version of my problem : I have measure which returns the revenues for the previous year : Revenues PY = CALCULATE(SUM(Data[Revenues]), DATEADD(DateTable[Date],-OffsetYear[OffsetYear Value],YEAR)) However, if there is a gap in my month context filter, the measure crashes : I understand that time intelligence functions cannot Apr 20, 2017 · Solved: I've read multiple posts on this error: "SAMEPERIODLASTYEAR expects a contiguous selection when the date column comes from a table on Nov 28, 2023 · your date table must be contiguous and covering. May 27, 2021 · Contiguous Selection - Date Column Comes from a Table on the 1-side of a Bi-directional Relationship. Apr 9, 2020 · So it is summing the entire year instead of the same period in the YTD measure. Apr 12, 2010 · LYTD Value = TOTALYTD ([Total value]; SAMEPERIODLASTYEAR( MAIN_SALES[Date])) I have 3 Charts, Region, Product, Store. in/learn-dax-powerbi/ ️ Power Query Course-https://goodly. If the report only references fiscal years, then the date table must include all the dates from the first to the last day of a fiscal year. Nov 29, 2022 · However, in my case, due to the narrow timeframe of dataset, currently I have data only for this year, and in every situation I want to compare two following quarters, so they are indeed contiguous. Jun 30, 2023 · The purpose of the SAMEPERIODLASTYEAR DAX function is to enable year-over-year analysis and comparison. Because bi-directional join as that may cause date in date dimension also getting filtered. 04-27-202002:02 PM. I'm trying out some DAX functions on the following data: I've set up this measure. I have a measure " Evo% " that allows me to calculate the percentage change in sales between years, the formula is: My date table has continuous values from 2018 to 2022 with no missing days: When I make my formula it worked perfectly and since yesterday I Dec 18, 2018 · Total Sales YTD Last Year = CALCULATE (FACT [Total Sales],SAMEPERIODLASTYEAR ('Date' [Date])) It works if my relationship between date table and FACT table are both ways but I get "Function 'SAMEPERIODLASTYEAR' expects a contiguous selection" if I filter on a specific shop. May 19, 2022 · DATEADD, SAMEPERIODLASTYEAR – DAX Guide. When adding a slicer that select Month of Year to-from I also get the eyar to date values from previous years. https://dax. Además de la respuesta de amitchandak, debe hacerlo solo unidireccional 1- Muchos desde la fecha hasta la tabla de datos. For example, the formula might be: [Revenue Last Year] = CALCULATE (SUM (Sales [Revenue]), SAMEPERIODLASTYEAR (Calendar [Date])) Apply the new calculated column or measure to your visualizations to compare current revenue with the same period last year. As the name implies, this function facilitates a comparison between a selected period and the equivalent period from the prior year, enabling Year-over-Year (YoY) analyses seamlessly. I think it would be more standard to pass the key of the date table to the function. 12-19-2022 11:06 PM. And what is meant here "date column is not unique"? Solved! Dec 15, 2022 · Community Support. Click H2. Jun 3, 2010 · ERROR – CALCULATION ABORTED: MdxScript(Sandbox) (22, 4) Function ‘DATEADD’ only works with contiguous date selections. But clearly, the issue isnt from my date table, or conflict would also arise in the visual matrix with date / material / NS of SAMEPERIODLASTYEAR(). The dates returned by SAMEPERIODLASTYEAR are the same as the dates returned by this equivalent formula: DATEADD(dates, -1, year) Other related threads: Function 'SAMEPERIODLASTYEAR' expects a contiguous selection Issue May 18, 2018 · I had this issue and changing the sameperiodlastyear from my year-month column to the full date column as suggested solved the issue, thanks! skip to main content Skip to main content Nov 12, 2020 · SAMEPERIODLASTYEAR is a Power BI time intelligence function in DAX which returns a table that contains a column of dates in the current selection of previous year. Sorted by: 3. Aug 2, 2018 · Thank you. Nov 7, 2019 · I had this issue and changing the sameperiodlastyear from my year-month column to the full date column as suggested solved the issue, thanks! Message 16 of 17 4,534 Views Jul 10, 2023 · Write the DAX formula using the SAMEPERIODLASTYEAR function. Mar 11, 2020 · SAMEPERIODLASTYEAR for Partial Year. Hello, After searching and reading multiple articles regarding this, and yet to find a solution, I hope that some can come to my rescue. I have sales table, product table and calendar table . However, it seems that, if I change the relationshipt between the Date dimention table and Sales from direction from "Both" to "Single" it works. Note: The dates argument can be any of the following: A reference to a date/time column, A table expression that returns a single column of date/time values, A Boolean expression that defines a single-column table of date/time values. Dec 18, 2023 · Finally, we discussed two important types of functions for working with dates: DATEADD and SAMEPERIODLASTYEAR. Sep 5, 2023 · In the realm of time-intelligence functions, SAMEPERIODLASTYEAR()stands out as a cornerstone in Power BI's DAX arsenal. – Mik. 05-27-2021 12:45 PM. si está intentando utilizar estas funciones de inteligencia de tiempo como SAMEPERIODLASTYEAR o DATEADD, necesita un intervalo contiguo Jan 31, 2021 · The function SAMEPERIODLASTYEAR gives you all visible dates in the data table and then offset them for a year. 1. Jul 10, 2023 · The SAMEPERIODLASTYEAR function plays a crucial role in scenario analysis by allowing us to compare data from the current period with data from the same period in the previous year. Both a slicer that selects year or a matrix with year as rows works and gives me the YTD values. When I click (Cross highlight) one of the items in the Region Chart the Product chart throws out that error, but the strange thing is that the Store chart doesnt. Dec 12, 2018 · It works if my relationship between date table and FACT table are both ways but I get "Function 'SAMEPERIODLASTYEAR' expects a contiguous selection" if I filter on a specific shop. It allows users to fetch data from the previous year’s corresponding period and incorporate it into their calculations or visualizations. Oct 4, 2023 · (Function 'SAMEPERIODLASTYEAR' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion. I've also created a new Measure: Sales SPLY = Calculate([Total Sales],SAMEPERIODLASTYEAR('Date Table'[Date])) But when I use this measure in a simple Order date: Total sales table, it seems to ignore Aug 4, 2016 · It should now be something like: YTD LY = CALCULATE([YTD],SAMEPERIODLASTYEAR('Date'[DateKey])) I am also a fan of: YTD LY = CALCULATE([YTD],DATEADD('Date'[DateKey], -1, year)) One example only. Porque la combinación bidireccional, ya que puede provocar que la dimensión de fecha en la fecha también se filtre. Is there any way to define last year value as zero for all those cases where no value exists? Oct 26, 2022 · Function 'SAMEPERIODLASTYEAR' expects a contiguous. I have slicers for FY, Month and Product. Rest numbers are correct! To be able to work only with the year total, I’ll need to use the function HASONEVALUE. This function does not support DirectQuery mode. Nov 3, 2018 · Power BI - DAX measure to calculate sales first 31 days - 'DATEADD' expects a contiguous selection. Sep 2, 2023 · Hi. Hi everyone, I'm using a SQL DB (DirectQuery mode) as a datasource for my report. May 30, 2022 at 6:11. My goal is to have a report with: - 2 slicers: 1 for years; 1 for months. Apr 20, 2017 · Solved: I've read multiple posts on this error: "SAMEPERIODLASTYEAR expects a contiguous selection when the date column comes from a table on May 26, 2019 · DATEADD "expects a contiguous selection when the date column is not unique, has gaps" 05-26-2019 04:23 AM. For example, if the fiscal year 2008 starts on July 1, 2007, then the Date table must include all Sep 3, 2021 · The "SAMEPERIODLASTYEAR" function expects a contiguous selection. May 3, 2023 · You are supposed to use a calendar table with contiguous dates and full coverage of both the source and the result of any time intelligence calculation. SAMEPERIODLASTYEAR contiguous selection issue yesterday I have a date master table against which my SAMEPERIODLASTYEAR DAX works fine however I have another report so the user can select a rolling 12 month date range (Date Range Selection) which has a 1 - Many relationship back to the date master table. Step 3: To use the SAMEPERIODLASTYEAR function, start by typing “=SAMEPERIODLASTYEAR (” and specify the column or measure to evaluate. The dates returned by SAMEPERIODLASTYEAR are the same as the dates returned by this equivalent formula: DATEADD(dates, -1, year) Other related threads: Function 'SAMEPERIODLASTYEAR' expects a contiguous selection Issue Dec 12, 2018 · It works if my relationship between date table and FACT table are both ways but I get "Function 'SAMEPERIODLASTYEAR' expects a contiguous selection" if I filter on a specific shop. The DAX expression I was using was as follows: =CALCULATE(SUM(FactInternetSales[SalesAmount]) Aug 17, 2022 · CALCULATE([Measure], SAMEPERIODLASTYEAR(Facttable[Date])) You can also try using filter and DATEADD . Create a new measure by clicking on the “New Measure” button in the “Modeling” tab. I did a search and this appears typically when there is slicers on the pageI have no slicers or page level filters so I'm not sure where to go from here Dec 11, 2018 · I resorted to using a more complex DAX formula because I was trying to find a workaround for the "Power bi expects a contiguous selection" issue. Oct 8, 2019 · Hi. This function enables us to assess how our data has performed over time, identify trends, and make year-on-year comparisons. SAMEPERIODLASTYEAR : Returns a set of dates in the current selection from the previous year. I need to get last year sales value for the current month selected. May 29, 2017 · What is contiguous selection? 05-29-2017 09:06 AM. Apr 27, 2020 · Hola @StephenK,. Specify the column or expression you want to compare from the previous year. Jul 10, 2023 · To use the SAMEPERIODLASTYEAR function in Power BI, you need to define the column or measure that represents the current period, along with the column or measure that contains the values you want to compare. I don’t really know if this is a bug, it is clearly in the definition. SYNTAX. com. Step 2: Once your data is loaded, create a new calculated column or measure in the DAX formula bar. Apr 25, 2024 · A Boolean expression that defines a single-column table of date/time values. Mark as date table でバリデーションが済んでいるから、ほとんどの場合で問題は起きない。しかし、フィルタコンテキストでギャップが発生することがあるのだ。 Apr 20, 2017 · How about this instead of using SAMEPERIODLASTYEAR: Fees Billed SPLY = CALCULATE([Fees Billed YTD],DATEADD(Table_BasicCalendarUS[DateKey],-1,YEAR)) Image Function-SAMEPERIODLASTYEAR-expects-a-contiguous-selection-Issue in Dale Tsui&#039;s images album We can then create a measure to calculate sales for the same period as the one involved in the context a year earlier: Ventas año anterior = CALCULATE ( [Ventas], SAMEPERIODLASTYEAR (DimDate [Datekey])) If we take this measure to the previous matrix, we obtain the following: It can be seen how, indeed, this last measure returns the sum of Apr 20, 2017 · I had this issue and changing the sameperiodlastyear from my year-month column to the full date column as suggested solved the issue, thanks! Message 16 of 17 4,499 Views Apr 20, 2017 · I had this issue and changing the sameperiodlastyear from my year-month column to the full date column as suggested solved the issue, thanks! Message 16 of 17 4,488 Views Apr 20, 2017 · I had this issue and changing the sameperiodlastyear from my year-month column to the full date column as suggested solved the issue, thanks! Message 16 of 17 4,336 Views Apr 27, 2020 · Hola @StephenK,. 12-10-2018 12:35 PM. Enter a name for your measure and use the SAMEPERIODLASTYEAR function in the formula bar. It seems that the problem is that you are passing the date column in your fact table to the SamePeriodLastYear() function. Jul 7, 2021 · Calculation error in measure: Function 'DATEADD' expects a contiguous selection when the date column Apr 20, 2017 · Just curious, is your Table_BasicCalendarUS[DateKey]) of type date or something else? Jan 5, 2021 · - - - - My Courses - - - - ️ Mastering DAX in Power BI -https://goodly. Constraints on Boolean expressions are described in the topic, CALCULATE. This function is commonly used in scenarios where historical data comparison is crucial, such as sales Sep 4, 2020 · Function 'DATEADD' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion. co. And what is meant here "date column is not unique"? May 29, 2017 · Some functions only work with "contiguous selections". Can you share the solution, please? Please also close this thread. You will need to change both reflecting table and column names. Notice a couple of things. Ask Question Asked 5 years, 6 months ago. Best regards Apr 16, 2021 · Function 'SAMEPERIODLASTYEAR' expects a contiguous selection when the date column comes from a table on the 1-side of a bi-directional relationship. May 23, 2024 · The Date table must always start on January 1 and end on December 31, including all the days in this range. Take a look at the formula in Figure 4. Dec 11, 2018 · DIRECT QUERY equivalent to SAMEPERIODLASTYEAR. The function then calculates the values for the same period in the previous year, providing a basis for accurate year-over-year comparisons. For example, the “Paid” description (highlighted on the left and shown by month in the table to the right below) has data in all of 2022 but only shows data in April of 2021 and August thru December of 2021. 09-03-2021 12:50 AM. Then I changed to single relationship and the result for the previous year is wrong: Total Sales YTD Last Year gives me the total sales for 2017 instead of same period. Then I created a connection between that and the "Raised Date" within my QI, also properly setup as a date. Be sure to use the Date Key for the relationship and the DAX. si está intentando utilizar estas funciones de inteligencia de tiempo como SAMEPERIODLASTYEAR o DATEADD, necesita un intervalo contiguo May 26, 2019 · DATEADD "expects a contiguous selection when the date column is not unique, has gaps" 05-26-2019 04:23 AM. Jul 9, 2020 · I had this issue and changing the sameperiodlastyear from my year-month column to the full date column as suggested solved the issue, thanks! Message 16 of 17 4,871 Views Mar 9, 2020 · I applied that as a filter for my visual card for Same Period Last Year Sales and only select "Past" (below is my measure for the value that the card displays): Same Period Last Year = CALCULATE (SUM (Table [Sales]), SAMEPERIODLASTYEAR (Calendar [Date]) May 29, 2017 · Some functions only work with "contiguous selections". Then I changed to single relationship and the result for the previous Dec 12, 2018 · It works if my relationship between date table and FACT table are both ways but I get "Function 'SAMEPERIODLASTYEAR' expects a contiguous selection" if I filter on a specific shop. If the above ones can't help you, could you please provide which measure you are using the function SAMEPERIODLASTYEAR in and your expected result? As checked your shared file, I can't Dec 11, 2018 · It works if my relationship between date table and FACT table are both ways but I get "Function 'SAMEPERIODLASTYEAR' expects a contiguous selection" if I filter on a specific shop. Is there any way to define last year value as zero for all those cases where no value exists? Jul 10, 2023 · Open Power BI and load the dataset you want to work with. By leveraging the SAMEPERIODLASTYEAR . And also, based on this logic this should break both quarter names and numeric calculations. In addition to amitchandak's reply, you should make it only one-directional 1- Many from date to Data table. Apr 28, 2020 · SAMEPERIODLASTYEAR espera una selección contigua. DATEADD: Moves the given set of dates by a specified interval. 10-26-202204:16 PM. ” Both use the context to determine their range. Jul 10, 2023 · Step 1: Open Power BI and load your data into the Power Query Editor or connect to an existing data source. Sales YTD = Calculate (TOTALYTD (SUM (Sales [Sale£]),Sales [OrderDate])) and used it in the following table. SAMEPERIODLASTYEAR (<dates>) Lets look Thus, I have used the "DATEADD", as such: =CALCULATE ( [Total Jobs Affected],DATEADD (Calendar_Final [Date],-7,DAY)) Where Calendar_Final is my calendar I created , with what I thought was a properly setup table. ). What are they exactly? For example, Function 'SAMEPERIODLASTYEAR' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion. Jan 3, 2007 · Follow these easy steps to disable AdBlock 1)Click on the icon in the browser’s toolbar. Many time series functions require dates to be like this (“contiguous”) in order to work as intended. The SAMEPERIODLASTYEAR function (DAX) returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context. Dec 11, 2018 · It works if my relationship between date table and FACT table are both ways but I get "Function 'SAMEPERIODLASTYEAR' expects a contiguous selection" if I filter on a specific shop. I think this is a though one. Some functions only work with "contiguous selections". Best regards Oct 9, 2019 · Hi. I only want to modify the Sales LY = 18,327, for Year 2012 total. This requirement often leads to confusion and errors if not properly understood and addressed. Jan 24, 2023 · There seems to be an issue when a description type in the current year, is not present for the same periods last year. The dates returned are the same as the dates returned by this equivalent formula: DATEADD(dates, -1, year) This function is not supported for use in DirectQuery mode when used in calculated May 26, 2019 · DATEADD "expects a contiguous selection when the date column is not unique, has gaps" 05-26-2019 04:23 AM. Before start explaning, let me just note, that this approach has worked perfectly when Jan 20, 2021 · SAMEPERIODLASTYEAR Parameter: dates : A column containing dates. And what is meant here "date column is not unique"? Dec 11, 2023 · I am receiving a "only works with contigous data selections" when using the SAMEPERIODLASTYEAR function, and can't seem to figure out what is causing it. Do note that the dates are all in one column. Both functions offer unique ways to analyze temporal data, allowing users to easily SAMEPERIODLASTYEAR: "Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context. Sep 22, 2010 · SAMEPERIODLASTYEAR: “Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context. Dec 10, 2018 · I resorted to using a more complex DAX formula because I was trying to find a workaround for the "Power bi expects a contiguous selection" issue. Let’s tackle the second one. Sep 18, 2023 · SAMEPERIODLASTYEAR is a DAX function that returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context. I have a calendar table that is complete with no gaps or duplicates. Hello friends, how are you, I hope very well, I bring the following problem that I think there are already many posts but I have not been able to solve it, I am creating a relationship of current sales VS previous sales (1 year), using the 'SAMEPERIODLASTYEAR' function but Dec 18, 2018 · Hi , I'm glad you solved it. - 1 List visual with 3 columns: YearMonth, Sales Amt, SalesAmt Previous Year. if you are trying to use these time intelligence functions like SAMEPERIODLASTYEAR or DATEADD, It needs a contiguous interval in date column. The Essence of Time-Intelligence: May 29, 2017 · (Function 'SAMEPERIODLASTYEAR' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion. Aug 31, 2022 · CALCULATE([Measure], SAMEPERIODLASTYEAR(Facttable[Date])) You can also try using filter and DATEADD . Meaning no gaps for the current year and no gaps for the previous year (the target range of your time intelligence calculation). qv ni in jx dy ci eq ln xi hx