Stairway to DAX and Power BI Level 25: Time Intelligence – Dates Functions: The DAX CLOSINGBALANCE*() Functions (2023-08-30)

SQLServerCentral Newsletter for August 30, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to DAX and Power BI Level 25: Time Intelligence – Dates Functions: The DAX CLOSINGBALANCE*() Functions]( - [Let’s talk about Artificial Intelligence, ChatGPT and Gobbledegook]( - [From the SQL Server Central Blogs - Yet another newsletter]( - [From the SQL Server Central Blogs - Finding a History of Database Snapshots, Taken and Restored]( - [Azure SQL Revealed: A Guide to the Cloud for SQL Server]( Question of the Day - [Deprecated Restore Options]( The Voice of the DBA  The IT Jobs AI Can't Do This was a provocative title: [6 ITOps Skills That Will Never Be Automated](. In a time when AI use is growing quickly and many people fear for their jobs, it's nice to see someone writing about areas that AI will struggle to handle. Ironically, lots of these articles are written by writers without much technical expertise and who are more likely than others to struggle in the age of AI. If a computer can mimic styles, less writers will be employed. That might be fitting with so many journalists hyping technologies without really understanding them, doing so in a way that creates stress among actual IT professionals. The article lists 6 jobs: policy config, incident response, complex scaling, app feedback, ITOps tool deployments, and end-user support. Of these, I'm not sure that app feedback and end-user support are good-paying jobs that many people want, but I know there certainly are people working in these areas who depend on those jobs. The others are often jobs that few people do. I do think that these are complex jobs where a computer can't really take in enough information to do a great job here, but I do think that one smart worker could learn to guide an AI that does a lot of the busy work. Someone could specify a policy from an AI and then ask the AI to tweak it as holes or gaps are identified. I think over time a lot of quick incident response items could be handled by an AI, albeit with a human guiding it. Again, that reduces the need for humans in these roles. To me, that's where the AI revolution becomes scary. AI is a lever to get a lot done. Fewer humans can be used to accomplish tasks with the aid of AIs, which reduces a lot of the labor needed. After all, we know that in any area, there are lots of beginners and advanced beginners doing work. If we can use an AI assistant to help one or two humans, we might remove the need for a dozen others. Maybe that's what a future [10x engineer]( looks like. Someone that's way more productive than many others because they've leveraged the computer (in the form of an AI) to get a tremendous amount of work done. I do think there are likely jobs that AIs can't easily do, but I also think that a lot of the work in these areas might get copy/pasted between organizations. Policies, scaling choices, and more are things that one human might learn from others and mimic those efforts. With better judgment than an AI, but not as much as if a true expert were coming up with the best solution for a particular situation. Unfortunately, most of us don't come up with optimal solutions, and our organizations run fine with sub-optimal code/policies/decisions/whatever. Will companies get by with very few highly paid people or a few more low ones and accept mediocre software? That already happens far too often already. I suspect like most trends, we'll see quite a few organizations move in this direction. However, I think the human ingenuity will win out and as a few companies realize that the creativity of humans can better target their goals than an AI that merely summarizes other work, the pendulum will swing back. Unfortunately, a lot of people will get caught in bad situations as this happens. Work on your skills, technical and soft, and be sure that you are in the best position you can be in as the world embraces and evolves with AI technology. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Technical Article]( [Stairway to DAX and Power BI Level 25: Time Intelligence – Dates Functions: The DAX CLOSINGBALANCE*() Functions]( Bill Pearson from SQLServerCentral Business Intelligence Architect, Analysis Services Maestro, and author Bill Pearson introduces three similar DAX Time Intelligence functions related to Date: CLOSINGBALANCEMONTH(), CLOSINGBALANCEQUARTER(), and CLOSINGBALANCEYEAR(). He discusses the syntax, uses and operation of each function, and then provides hands-on exposure to it in Power BI. [External Article]( [Let’s talk about Artificial Intelligence, ChatGPT and Gobbledegook]( Additional Articles from SimpleTalk I use Chat-GPT occasionally, instead of Google, to get answers to technical questions. However, I regard Chat-GPT as an indiscriminate conveyor of existing information, not artificial intelligence. [Blog Post]( From the SQL Server Central Blogs - [Yet another newsletter]( Diligentdba 46159 from Mala's Data Blog I’ve been thinking about starting a newsletter for a while now. I want to focus on summaries and what stood out for me from among the many cool talks... From the SQL Server Central Blogs - [Finding a History of Database Snapshots, Taken and Restored]( david.fowler 42596 from SQL Undercover This is a question that’s come up twice this morning, firstly where can we find a history of database snapshots and secondly where can we find a history of... [Azure SQL Revealed]( [Azure SQL Revealed: A Guide to the Cloud for SQL Server]( Site Owners from SQLServerCentral Access detailed content and examples on Azure SQL, a set of cloud services that allows for SQL Server to be deployed in the cloud. This book teaches the fundamentals of deployment, configuration, security, performance, and availability of Azure SQL from the perspective of these same tasks and capabilities in SQL Server. This distinct approach makes this book an ideal learning platform for readers familiar with SQL Server on-premises who want to migrate their skills toward providing cloud solutions to an enterprise market that is increasingly cloud-focused.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Deprecated Restore Options Which of these options is deprecated for the RESTORE command? 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) sp_refreshsqlmodule Parameters There are two parameters for sp_refreshsqlmodule, @name and @namespace. What are the requirements for these parameters? Answer: name is required, namespace is optional unless a DDL trigger is given as the name Explanation: The @name is required and can be a multi-part identifier. The namespace is only required (or allowed) when the name given is a DDL trigger. Then this must be DATABASE_DDL_TRIGGER or SERVER_DDL_TRIGGER Ref: sp_refreshsqlmodule - [ [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 2017 - Administration [Select blocking a Truncate stamemet]( - Hi All, I am seeing a SELECT blocking a TRUNCATE statement. THE SELECT is running over 4 hours and it is waitype is AYSN_NETWORK_IO. Isolation level is default read committed isolation level. Why or when does a SELECT takes "Sch-S” lock? I see this lock being acquired using sp_whoisactive. Truncate is waiting for="Sch-M" lock. How […] [What's the right way of capturing expensive queries?]( - Hi All, While capturing expensive queries by CPU or IO, why it is important to group queries by query_hash and then tune them instead of directly pulling top 10 queries from sys.dm_exec_query_stats DMV?. What am I missing ? is it wrong way to capture expensive queries> if so, I want to understand why? please share […] [High CPU & blocking]( - Hello SQL Experts, We have SQL2017 enterprise edition with about 60 databases. Recently we are seeing very high cpu spikes. We have DB maintenance job running twice/wk (Hallenger Script). Manually updated the statistics but it didn't helpful. I don't have baseline about the no of transactions or queries that ran previously. Followed the guidance provided […] SQL Server 2017 - Development [Join on Column A when Column B is Null]( - hi, I need to join on a table T1 AND Table T2. Something like this : Select * from Table T1 JOIN TableT2 on T1.A =T2.A but here , condition is if (T1.a or t2.a is null ) then join on (T1.b =t2.b ) where t1.b and t2.b is not null.  How to achieve […] Administration - SQL Server 2014 [SQL 2014 Standard - All SQL agent jobs disabled after patch]( - We have a SQL server 2014 (with SSRS) and over the weekend the list of patches below got installed around 1AM. KB5029312 KB5028970 KB5028957 KB5029368 The following morning it was noticed that no jobs ran and when we checked, EVERY single SQL agent job had a red x and were all disabled. I have never […] SQL 2012 - General [The target princial name is incorrect. Cannot generate SSPI context.]( - I've just created new certs, new SPNS and applied via SSCM. Within AD I've ticked trust this user for delegation, use kerberos only to all of my new SPNS. SQL starts fine & I can logon locally but get the above error logging on from a different machine (therefore attempting kerberos). I'm sure I've done […] [The target princial name is incorrect. Cannot generate SSPI context.]( - I've just created new certs, new SPNS and applied via SSCM. Within AD I've ticked trust this user for delegation, use kerberos only to all of my new SPNS. SQL starts fine & I can logon locally but get the above error logging on from a different machine (therefore attempting kerberos). I'm sure I've done […] SQL Server 2019 - Administration [msdb job history permission error]( - SELECT * FROM msdb.dbo.sysjobhistory error: The SELECT permission was denied on the object 'sysjobhistory', database 'msdb', schema 'dbo'.  the user is a member of the following msdb role: SQLAgentReaderRole SQLAgentUserRole SQLAgentOperatorRole  What other permissions do i need? [How to Backup SQL Active/Passive cluster Database to File Share]( - Hi We have 2 node Active/Passive SQL serer cluster with 2 databases, due to the backup disk capacity limitations we are planned to backup databases to the File Share location (\\192.168.0.x\Backup). When we trying to connect File Share to the WSFC there is no option to add file share it is showing only the availble […] [Query joins and SQL Server mirroring (2019)]( - Have a bit of a situation with database mirroring and table joins, hopefully someone can shed some light on possible solutions. Scenario: DB01 is on Server01 and is mirrored to Server02 DB02 is on Server01 and is mirrored to Server02 DB03 is on Server 03 and is mirrored to Server04 Linked servers are setup between […] [Color Particular Column]( - Hi I want to color particular column . If value is -ve then color should be red Thanks SQL Server 2019 - Development [Powershell script run through Task Scheduler - Cannot validate argument on param]( - I have a Powershell script which is being called through Windows Task Scheduler My script is set up as follows:  param( $FileDirectory, $VerboseDirectory, $ServerInstance, $Database ) Invoke-Sqlcmd -ServerInstance $ServerInstance -Database $Database -Query "EXEC spImportReturn @FileDirectory = '$FileDirectory'" -verbose 4> $VerboseDirectory Now I then call this Powershell script from Windows Task Scheduler as follows: Executable: […] Powershell [change delimeter]( - This script converts from xlsx to csv, but how can I change delimiter from comma to semicolon, and pickup the same file name from multiple folders to process? Thanks. # To execute the script without agreeing with the execution policy Set-ExecutionPolicy Bypass -Scope Process # Defines the directory where the file is located $dir = […] Integration Services [Job Step Properties - Configuration - Parameter Issue]( - Hi, I am fairly certain that both myself and my colleagues could ALL change the configuration parameter on a Job Step, but I am now informed that only a Sys Admin or the job owner can change this value. ( currently on SSMS 18.12.1 ) This is a bit annoying as it makes being able […] SQL Server 2022 - Development [Proc and Table permissions (Grantor column)]( - I have several tables and stored procedures that have an user ID of a person that is no longer with the company that is listed in the Grantor Column under schema properties permissions window. We ran into an issue with a stored procedure executing until we change the Grantor (the user Id) Column to a […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -