Stairway to DAX and Power BI Level 26: Time Intelligence – Dates Functions: The DAX “Total to Date” Functions (2023-09-27)

SQLServerCentral Newsletter for September 27, 2023 Problems displaying this newsletter? [View online](. [SQL Server Central]( Featured Contents - [Stairway to DAX and Power BI Level 26: Time Intelligence – Dates Functions: The DAX “Total to Date” Functions]( - [Introduction to Using Bitbucket and CI/CD for Your App]( - [The pros and cons of multicloud networking]( - [From the SQL Server Central Blogs - SQL Homework – September 2023 – Time for temporal tables.]( - [From the SQL Server Central Blogs - How to Secure SQL Server]( - [Practical Graph Structures in SQL Server and Azure SQL: Enabling Deeper Insights Using Highly Connected Data]( Question of the Day - [Creating Two Keys]( The Voice of the DBA  Running a Service Many of us work for some organization and we're responsible for managing a database or coding some application or some other data related position. Perhaps we ensure reports work for our users or move data around. In many cases, we have customers, whether they are internal to our organization or perhaps external from another business or even the wider public. However, many of us see our jobs as a part of the org/business and not that we are actually providing some service to others that we are responsible for. While I've certainly felt that way, I learned at some point that if I were an app dev, I ought to treat people using my app as customers, even though they don't pay me in currency. They often pay me in praise, or better yet, with praise to my boss. As a DBA, I treated both app devs and users as customers. That helped me take pride in my work and learn to view my position from other points of view. There was a post on [learnings from running a SaaS service for a year](. While few of us maintain a SaaS service, we could think of the app we build as Software-as-a-service, albeit without any revenue. We could see databases we manage as DBaaS (databases as a service) or PaaS (platform as a service) items. I think that the way we view the world, or view our situations, can be helpful to us in managing our workloads. We can better triage what is critical, what's important, and what's nice to have. We can decide when an issue deserves some to cause some stress and we might give an extra effort, and when something can wait until tomorrow. We can decide when we push back on demands and when we accommodate them. This approach has also helped me to think about ways to improve the service/software/platform I provide to others in ways that helps my customers, or makes my job easier. This way of thinking leads me to make the situation better when I can. It also helps me to discuss the decisions with others, especially management, in terms that can matter to them. Are we finding ways to improve the overall system. It doesn't always work, but it often has for me. And if you're interested in your own side project, the journey continues in [part two](. Perhaps you can spend an hour or two a day and build something that becomes your own business. That's what happened to me with SQL Server Central. Steve Jones - SSC Editor [Join the debate, and respond to today's editorial on the forums](   Featured Contents [Stairway to DAX and Power BI]( [Stairway to DAX and Power BI Level 26: Time Intelligence – Dates Functions: The DAX “Total to Date” 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: TOTALMTD(), TOTALQTD(), and TOTALMYD(). He discusses the syntax, uses and operation of each function, and then provides hands-on exposure to it in Power BI. [External Article]( [Introduction to Using Bitbucket and CI/CD for Your App]( Additional Articles from SimpleTalk If you have been in the DevOps space, you should know about version control and must have worked with 0ne. But, have you heard of BitBucket? No? Well, this tutorial is for you. [Technical Article]( [The pros and cons of multicloud networking]( Additional Articles from PluralSight Multicloud isn't always a sign of cloud maturity. In this post, we cover the pros and cons of multicloud networking and why you need a strategy to succeed. [Blog Post]( From the SQL Server Central Blogs - [SQL Homework – September 2023 – Time for temporal tables.]( Kenneth.Fisher from SQLStudies Recently I needed to use temporal tables. And not just for the job history table in a managed instance. In ... Continue reading [Blog Post]( From the SQL Server Central Blogs - [How to Secure SQL Server]( Hemantgiri S. Goswami from SQL Server Citation - SQL Blog by Hemantgiri S. Goswami, SQL MVP Security! This is the word that comes to mind of every concerned person when it comes to storing, accessing, and sharing the data and database or database server. At times when applications are run in geographically restricted areas, there... [Practical Graph Structures in SQL Server and Azure SQL: Enabling Deeper Insights Using Highly Connected Data]( Steve Jones - SSC Editor from SQLServerCentral Use the graph table features in Azure SQL that were introduced in SQL Server 2017 and further refined in SQL Server 2019. This book shows you how to create data structures to capture complex connections between items in your data. These connections will help you analyze and draw insights from connections in your data that go beyond classic relationships.   Question of the Day Today's question (by Steve Jones - SSC Editor):  Creating Two Keys On SQL Server 2019, I run this code. What happens? CREATE SYMMETRIC KEY PIISymKey WITH ALGORITHM = AES_128 , IDENTITY_VALUE = 'S0methingN3w' , KEY_SOURCE = 'Someth!ngBl&e' ENCRYPTION BY PASSWORD = 'MyS3cr#tP@ssword'; CREATE SYMMETRIC KEY HIPPASymKey WITH ALGORITHM = AES_128 , IDENTITY_VALUE = 'S0methingN3w' , KEY_SOURCE = 'Someth!ngBl&e' ENCRYPTION BY PASSWORD = 'MyS3cr#tP@ssword'; 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 Upgraded Procedure part 2 I had a SQL Server 2014 database. In it, I created this function: CREATE FUNCTION OPENJSON (@json varchar(1000)) RETURNS TABLE AS RETURN SELECT CASE WHEN SUBSTRING (@json, 1, 1) = '{' THEN 1 ELSE 0 END AS json_string; This works, and this call: SELECT * FROM OPENJSON('{ "key":1 }') Returns this result: json_string -------------- 1 Now I restore this database on a SQL Server 2019 instance and set the compatibility level to 150. I run this code: SELECT * FROM dbo.OPENJSON('{ "key":1 }') What happens? Answer: I get an incorrect syntax error Explanation: Once I upgrade the database and compatibility level, the OPENJSON function becomes the valid syntax. The UDF is not longer valid. Ref: ALTER DATABASE - [ [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 [Public Role Permissions concern]( - All, I've been double-checking security for one of my servers and I found the below permissions on the public role. I'm trying to decide if those are default / system-granted permissions that are necessary for the functioning of SQL Server or if these might have been granted by someone some time ago. Does anyone know? […] SQL Server 2016 - Administration [SSRS on Alwayson Availability Group]( - Hi, Installed SSRS feature in Primary replica and configured ReportServer and ReportTEMPDB by using SQL listener name. ReportServer and ReportTEMPDB brought into Availability Database and two databases are synchronized between two primary and secondary replica. SSRS reports rdl files kept it Node1 and able access reports � working as expected and tested AG failover between […] SQL Server 2016 - Development and T-SQL [How to get total sum across the table and sum grouped by date ranges]( - Here is sample code and what I tried to achieve, but not getting the output, any advice? select k.charges, sum(case when k.range>='101' then charges else 0 end) as '101charges', sum(case when k.range>='201' then charges else 0 end) as '201charge' from (select charges,substring(daterange,1,3) as range from dbo.charges, )k   CREATE TABLE charges ( charges money […] [Converting file name MMDDYYYY.txt to a Date Field]( - Hello, Converting file name MMDDYYYY.txt to a Date Field but CONVERT or CAST do not seem to work perhaps because I also have a REPLACE? Can someone help me understand how to do this if it is possible? See sample code below and many thanks in advance! CREATE TABLE #t (getFileName varchar(100)) INSERT INTO #T […] SQL Server 2019 - Development [extracting data from JSON with TSQL]( - Hi, I need help extracting data from the below JSON. What I need is the data from the "frequencyData" array. on the first row of the resultset I would like the first column to contain the first data sample of the "frequency"child array, the second column should contain the first data sample of the "df" […] [Value does not fall within the expected range. (Microsoft.SqlServer.ManagedDTS)]( - Good morning experts, Over the past few releases, my team and I have been running into this issue where when we deploy an SSIS Project from one SQL Server to another we get this error on the "Changing Protection Level part: Value does not fall within the expected range. (Microsoft.SqlServer.ManagedDTS) We have about 15 projects […] [Script for deleting data from a table and corresponding FKTables]( - Hi, Looking for a script to delete data from a table and also from the FK tables of data exists. I have seen some online using procedures and function but just looking for a base script SQL Server 2008 - General [P2V os sql vm to new host]( - I have used P2V to replicate my sql vm to a new host alongside all its disks storage, the problem is that these disks are part of a failover cluster and when I try to bring them online on the new host the following error occurs: The disk must be in cluster maintenance mode and […] SSDT [Partial Model Project With Same DB Reference]( - Hello, I am trying to create an SSDT project (in VS2022) for a database whose objects are primarily owned by a vendor application. This application dynamically creates views that we interface with from 'under-the-hood' tables that have non human-readable names. We have some custom integration code including some procedures, views, and functions, which are in-house […] Analysis Services [How to connect SSAS TABULER with mysql data source]( - Hello I have a mysql data source and I want to use it as data source to my analysis service… I could connect it through ssis and ssrs using odbc data source but I face a hard time finding odbc or ado.net data source in my ssas… can anyone help me with steps and photos […] SQL Server 2022 - Administration [Kubernetes Hands-On Courses]( - Hello, All I appreciate it may not be directly relevant to SQL Server, but I will be grateful if someone can recommend me (a company I work for are ready to chip in) a good Kubernetes course with practical element in it, please. There are plenty of free courses over the internet or event on […] [SQL Job on server set to UTC. Are jobs that perform GETDATE using UTC?]( - This is a scenario I haven't had to consider. We have a UTC time set SQL Server running jobs. We have set the jobs to run in based on UTC time converted from the timezone we're in (+10 hours) eg 6:45pm UTC (4:45am AEST) However one of the jobs calls a view that uses a […] SQL Server 2022 - Development [Both values between Start and End date]( - good afternoon, I'm trying to determine if both values (101 and 102) both occur within a 'Shift' (startdate/enddate). It originally just needed 101, but our facility added 102 and for the 'goal' to be compliant (1), they need to be both present within a shift. I'm having a bit of trouble working this out within […] [Creating a subquery which conflicts with the where clause...]( - Hi All, I have created a table named issues where incidents are being tracked. Every record has a master indicator column ("Y" or "N")which denotes that its the master incident. If the same incident type is created by another user, then the Master Indicator will be set to N and the master_issue column will be […] [Database Query Optimization: How to Improve Query Performance in SQL]( - I have a SQL database that stores a large amount of data, and I'm experiencing slow query performance when retrieving information. What are some strategies and best practices for optimizing database queries in SQL to achieve better performance? Here's an example query I'm working with: SELECT first_name, last_name, email FROM users WHERE registration_date >= '2023-01-01' […]   [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  - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -