Level 27: Time Intelligence – Dates Functions: The DAX “Parallel Period” and “Same Period Last Year” Functions (2024-01-24)

SQLServerCentral Newsletter for January 24, 2024 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Level 27: Time Intelligence – Dates Functions: The DAX “Parallel Period” and “Same Period Last Year” Functions]( - [Code Visibility: Browsing through Flyway Migration Files]( - [From the SQL Server Central Blogs - A temper, soft skills, and almost losing a job : T-SQL Tuesday #170]( - [From the SQL Server Central Blogs - Switching between different active physical relationships in a Power BI model]( - [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Question of the Day - [Getting a Schema Copy]( The Voice of the DBA  Take A Vote and Accept Your Loss I feel differently today than in the past about many of the things I've seen technical people argue about. I've written about [Tabs vs. Spaces]( and [Singular vs. Plural]( and others have debated commas before or after among other topics. While these might be interesting sidebars at lunch, I see them sometimes devolve into time sinks with teams revisiting the issues over and over during their daily work. These [types of religious wars]( stifle a lot of productivity and often can linger for years. However, in many cases what I see is debate across weeks or months and then time spent to shift the way that large groups of people work inside of a company. In the last few years, I've seen customers argue about which VCS to use, which new CI tool to choose, or even about which secret store to use for their database credentials. Often these debates happen when there is already a technology in use. In most cases, the differences between many of these arguments are negligible. Lots of teams fall down on either side of a debate and find themselves very productive. Or not productive, but it often seems the difference is the staff, not the tool, platform, language, or style. Good people are productive no matter which way we choose to work. My view is that for most of these items, we ought to have a (relatively) short meeting. Give each side a few days to prepare, but then one spokesperson for each side gets 5 minutes to present their case on why the group should adopt their idea. Once everyone has presented, we debate for a limited time, maybe 15-20 minutes, vote, and then move in that direction. Ultimately, we're trying to get software written (or deployed or managed or something) and not trying to decide the best way to format that code or choose a tool for CI/CD. This teaches people to communicate and learn to present a rational, coherent, succinct idea, which is a valuable skill. This also teaches us to work as a team and learn to accept decisions that don't go our way. None of us wins 100% of the time in life, so make a good effort to lead others in your direction, but accept that they might choose a different path. In that case, learn to support the team in their efforts. The caveat to all of this is that inside of an organization, we often want a standard, so if something is already heavily used, just adopt that pattern or technology. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to DAX and Power BI]( [Level 27: Time Intelligence – Dates Functions: The DAX “Parallel Period” and “Same Period Last Year” Functions]( Bill Pearson from SQLServerCentral Business Intelligence Architect, “Analysis Services Maestro, and author Bill Pearson introduces two somewhat similar DAX Time Intelligence functions related to “parallel” Date periods: PARALLELPERIOD() and SAMEPERIODLASTYEAR(). He discusses the syntax, uses and operation of each function, and then provides hands-on exposure to it in Power BI. [External Article]( [Code Visibility: Browsing through Flyway Migration Files]( Additional Articles from Redgate If you can convert a SQL file to HTML, then you can inspect your Flyway migration files in a browser. This is especially useful if your SQL is color-coded with the same conventions as it was in your IDE. It is even better still if your browser can allow you to scan through many files, moving from file to file with a single click. This article will demonstrate how to do this with a few PowerShell scripts. [Blog Post]( From the SQL Server Central Blogs - [A temper, soft skills, and almost losing a job : T-SQL Tuesday #170]( Kenneth.Fisher from SQLStudies It’s T-SQL Tuesday again! In fact, it’s the first of the year and our intrepid host is Reitse Eskens (blog|twitter). ... Continue reading [Blog Post]( From the SQL Server Central Blogs - [Switching between different active physical relationships in a Power BI model]( Meagan Longoria from Data Savvy A couple of weeks ago, I encountered a DAX question that I had not previously considered. They had a situation where there were two paths between two tables: on... [Transact-SQL: The Building Blocks to SQL Server Programming eBook by Gregory A. Larsen]( [Transact-SQL: The Building Blocks to SQL Server Programming by Gregory A. Larsen]( Greg Larsen from SQLServerCentral Transact SQL (TSQL) is the languaged used to query and update data stored in a SQL Server. This book, written by SQL Server Central and Simple Talk author Greg Larsen, will give developers an understanding of the basics of the TSQL language. Programmers will have the building blocks necessary to quickly and easily build applications that use SQL Server.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Getting a Schema Copy I want to get my schema from a SQL Server 2022 into a DACPAC format that I can use to apply to another database. What options do I use with SqlPackage.exe? Think you know the answer? [Click here]( and find out if you are right.    Yesterday's Question of the Day (by Steve Jones - SSC Editor) The Certificate User I want to run this on my SQL Server 2022 instance. I have a master key in the database that is open and the ETL schema exists. What happens with this code? CREATE CERTIFICATE ETLUserCert WITH SUBJECT = 'ETL User', EXPIRY_DATE = '11/11/2024'; GO CREATE USER ETLUser FOR CERTIFICATE ETLUserCert WITH DEFAULT_SCHEMA = ETL; GO Answer: The user is not created Explanation: In this case, the user is not created. You cannot use the default_schema clause with the CREATE USER FOR CERTIFICATE. You will get this error: Msg 15259, Level 16, State 1, Line 9 The DEFAULT_SCHEMA clause cannot be used with principals mapped to certificates or asymmetric keys. Ref: CREATE USER - [ [Discuss this question and answer on the forums](    Database Pros Who Need Your Help Here's a few of the new posts today on the forums. To see more, [visit the forums](. --------------------------------------------------------------- SQL Server 2016 - Administration [How to write a script that kills a process if it's blocking over 1 minute]( - Please enlighten me on how to write a script that kills an event if it's blocking another process over 1 minute. In theory I would run this script via SQL server agent, every 10 minutes, for 24 hours.  Thank you SQL Server 2016 - Development and T-SQL [what parts of an ssas tabular cube should i version in tfs/dev ops]( - Hi, i want to start versioning the critical portions of my ssas tabular cube in tfs. What should i archive the bim file? [calling a report from a matrix]( - Hi. I have a dataset that returns a row for/with every invoice in the parameter range you see below.  It is bound to the tablix you see below. In the cell(s) where it says "call a report" i have an action to go to a sub report that accepts the passed invoice as a parameter. […] [home grown resource governor]( - Hi. I just read the documentation on setting up the RG and didnt like it. Isnt there a way to write a quick home grown one? I want to regulate just one user's query run times he'll be writing and running from ssms.  Its kind of a trial thing.  We dont usually give ssms […] SQL 2012 - General [Database mail quit working]( - About a week ago, database mail stopped working. The error message from the log is "Cannot send mails to mail server (Failure sending mail)." the rather generic error message. This is happening both on test emails as well as other production emails I restarted the server, tried temporarily disabling the firewall, recreated the db mail […] SQL Server 2019 - Administration [Logging Windows Event Log data to SQL Server table]( - There are a couple of excellent articles on the subject of importing Windows Event Log data to sql table. Like this for example: Send Windows Event Logs to SQL Database my TWO BIG QUESTIONS ARE: 1 - how important/useful (for what?) will such storage of Win Event log data in sql server table? in what situation […] [SQL Server Agent Not Updating Job Next Run Date]( - Hello, I use SQL 2019. I have a job that runs yearly. For some odd reason the date changed on it. Now when I try to set a new date for it under job activity monitor it will just stay on the incorrect date. I just updated the Windows, and restarted it. I also restarted […] [Index performance: Can Mix of lower and upper case cause issues?]( - folks i have a relatively large table (22mil records) to the rest of our sql tables using the application, i can query all transactions for a value B700 that has 9799 records in year 2023 in 2-3 seconds using the same application, i query C900 in year 2023 and it takes minutes to return data […] SQL Azure - Administration [Migrating SQL express database over to Azure]( - Hi all, My boss wants to move our customers over to azure from SQL express (mostly SQL Express, some are on SQL server.) I've created a .bak backup file of the SQL database and uploaded it to the blob storage but I've have no idea how to restore that .bak file in Azure. I've seen […] Reporting Services [Creating a table In SSRS that populates a SQL table]( - Hi i would imagine this is a straight forward request However i have a very simple 3 column table in SQL, and i would like the ability to have the option to insert into it from an SSRS report and potentially edit the data the SQL table consist of 3 colums ss_acode, ss_desc and ss_prod […] [ReportExecution2005.asmx]( - We have been using the ReportExecution2005.asmx endpoint in my C# web application to download SSRS reports.  We are turning on https on our SQL servers and need to know how to convert the following in my web.config file to work with https.  Obviously, the endpoint address will change to https.  I just don't know how […] Analysis Services [Cube Partitions Source and Slice]( - Hello I'm struggling to find a definitive answer as to why slice is required if Source is used Basically, if you take a fact that you want to partition based upon an accounting year, I would create multiple partitions (one per year) and use source as a filter for each one e.g. Sales_2020   […] SQL Server 2022 - Development [Does INSERT's affect performance depending on INSERT order?]( - Assume I have a table with a clustered datetime-index. Assume I'm going to insert lots of data. The data to be inserted is not in any specific order. Would the INSERT go faster if the data to be inserted is sorted by the datetime column? I am just guessing here but inserting random data here […] [Error converting datatype varchar to bigint]( - Hi Tried a few things here but can't seem to get past the 'Error converting datatype varchar to bigint' error: SELECT DISTINCT provTaxID ,provNPI ,provName ,provShortName ,' + provNPI BillProvURL_NPPES ,' + REPLACE( dbo.urlencode( provName , default), '%2B', '+' ) BillProvURL_Google ,ProvLookupSource ,provCategory ,format(CAST(provPhoneMain AS BIGINT), '###-###-####') provPhoneMain ,format(CAST(provPhoneFax AS BIGINT), '###-###-####') provPhoneFax ,provEmail FROM […] [Why is BigQuery Sandbox saying its ERROR: Expected keyword AS but got "(" at [1:]( - Is this an issue with BigQuerry. I was follwing along with this youtube video and for some reason this code will not work.  /// with pop_vs_vac (continent, location, date, population, new_vaccinations, rolling_people_vaccinated) as ( Select dea.continent, dea.location, dea.date, dea.population, vac.new_vaccinations, SUM(CASt(vac.new_vaccinations as int)) OVER (partition by dea.location order by dea.location, dea.date) as rolling_peoploe_vaccinated --(rollling_people_vaccinated/population)* 100 from […]   [RSS Feed]( This email has been sent to {EMAIL}. To be removed from this list, please click [here](. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com. ©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved. webmaster@sqlservercentral.com  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -