Calculating Previous Month total with Time Intelligence Function

Have you ever come across calculating previous month’s sales? It’s fascinating to learn how time intelligence function works.

I have come across two easy way to write the code to get the total sale of previous month. Either you can use the DATEADD() or PREVIOUSMONTH() functions to get the total sale from Total Sale measure.

Previous Month Sale = CALCULATE([Total Sale],DATEADD(dimDate[FullDate],-1,MONTH))

Previous Month Sale = CALCULATE([Total Sale],PREVIOUSMONTH(dimDate[FullDate]))

What I’m trying to explain here is when I used this DAX commands above measures, it ended up giving me blanks. Let me explain you how I resolved this issue and how I make it working.

Thanks to https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/ post, I added a dummy fact table into my model.

dummyTable =

FILTER (

    DATATABLE( "Date", DATETIME, { { BLANK() } } ),

    FALSE

)

According to the post I created 1:M cardinal in single cross filter direction(dimDate(1) to dummyTable(*)) as explained in the post.

Amazingly, my “Previous Month Sale” end up showing me the expected values.

Thanks Marco for this amazing article https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/

Convert Date from Integer to Date in DAX

I have a dimension table that contains only the “YearMonth” key. Now my target is to create the first date for each YearMonth key. I simply get this by concatenating it with 01 and change the date type into Whole Number.

DateIntColumn = CONCATENATE('Month'[MonthKey],"01")

My next target is what the reason I’m writing this post. How I’m going to get the date type filed out from this date number. What I do is I simply break it to ‘yyyy,mm,dd’. Below is the DAX I have used.

Column = DATE(INT(LEFT([DateIntColumn],4)),INT(MID([DateIntColumn],5,2)),INT(RIGHT([DateIntColumn],2)))

EWS Managed API integrate and pass email information to another system

In this blog i'm only explaining the important aspects when reading using EWS API. I read from a child folder from root mailbox. Use WellKnownFolderName.Root it will show all folders of my Inbox.Then using web services i call the 3rd party tool and pass the information read from the specific mail box. Below i'm showing only the flow of the process but this will help you to figure our the code level commands.

public static void RunProcess()
        {
            DateTime dateTime = DateTime.Now;


            service.Credentials = new NetworkCredential(Settings.Default.EmailAddress, Settings.Default.EmailPassword);

            if (Settings.Default.ExchangeUseAutodiscover == true)
            {
                if (isDebugEnabled)
                {
                    logger.Debug("Finding Autodiscover URL.");
                }
                service.AutodiscoverUrl(Settings.Default.EmailAddress, RedirectionCallback);
            }
            else
            {
                service.Url = new System.Uri(Settings.Default.ExchangeWebServicesEndpoint);
            }

            if (isDebugEnabled)
            {
                logger.Debug(String.Format("EWS Endpoint: {0}", service.Url));
            }

            BindMailboxFolders();
            ProcessEmail();
        }

        static bool RedirectionCallback(string url)
        {
            return url.ToLower().StartsWith("https://");
        }

 static void BindMailboxFolders()
        {
            FolderView view = new FolderView(100);
            view.PropertySet = new PropertySet(BasePropertySet.IdOnly);
            view.PropertySet.Add(FolderSchema.DisplayName);
            view.Traversal = FolderTraversal.Deep;
            FindFoldersResults findFolderResults = service.FindFolders(WellKnownFolderName.Root, view);

            foreach (Folder folder in findFolderResults)
            {
                if (folder.DisplayName.Equals(Settings.Default.CaseCreatedMailFolderName))
                    caseCreatedMailFolder = Folder.Bind(service, folder.Id);

                if (folder.DisplayName.Equals(Settings.Default.ErrorMailFolderName))
                    errorMailFolder = Folder.Bind(service, folder.Id);

                if (folder.DisplayName.Equals(Settings.Default.UnknownMailFolderName))
                    unknownMailFolder = Folder.Bind(service, folder.Id);
            }
        }

        static void ProcessEmail()
        {
            PropertySet emailPropertySet = new PropertySet(BasePropertySet.FirstClassProperties, EmailMessageSchema.IsRead);
            emailPropertySet.RequestedBodyType = BodyType.Text;
            ItemView itemView = new ItemView(1000);
            itemView.PropertySet = emailPropertySet;

            //Get unread email only
            SearchFilter filter = new SearchFilter.SearchFilterCollection(LogicalOperator.And, new SearchFilter.IsEqualTo(EmailMessageSchema.IsRead, false));
            FindItemsResults<Item> findEmailResults = service.FindItems(WellKnownFolderName.Inbox, filter, itemView);

             int emailCount = 1;
            Dictionary<int, Dictionary<string, string>> emails = new Dictionary<int, Dictionary<string, string>>();

            foreach (EmailMessage email in findEmailResults.Items.Where(i => i is EmailMessage))
            {
                email.Load(emailPropertySet);
                EmailData emailData = new EmailData();
                emailData.fromAddress = email.From.ToString();
                emailData.sent = email.DateTimeSent.ToString();
                emailData.toAddress = email.DisplayTo.ToString();
                emailData.mailSubject = email.Subject.ToString();

                if (isDebugEnabled)
                {
                    logger.Debug("-----------------------------------------------");
                    logger.Debug("From: " + email.LastModifiedName);
                    logger.Debug("To: " + email.DisplayTo);
                    logger.Debug("CC: " + email.DisplayCc);
                    logger.Debug("Subject: " + email.Subject);
                    logger.Debug("-----------------------------------------------");
                }

                emails.Add(emailCount, new Dictionary<string, string>());
                email.Body.ToString().Split('\n');
                foreach (string line in email.Body.ToString().Split('\n'))
                {
                    if (Regex.IsMatch(line, "^.*:{1}.*$"))
                    {
                        //Split only on first occurance of ":"
                        string[] values = line.Split(new[] { ":" }, 2, StringSplitOptions.None);
                        emails[emailCount].Add(values[0].Trim(), values[1].Trim());

                        if (isDebugEnabled)
                        {
                            logger.Debug(values[0].Trim() + " - " + values[1].Trim());
                        }
                    }
                }

                //Attempt to create case with web services here, then move into folder based on result of web services call
               
                // Get the Dictionary for the current email
                Dictionary<string, string> emailDictionary = emails[1];
                bool dataComplete = VerifyEmailProperties(emailData, emailDictionary);

                // Get the process to create the Case for
                Process process = processFactory.getProcess(emailData);

                // Create Case in 3rd party system by calling it's web service
                try
                {
                    createCase.processRequest(process);

                    //Mark as read and move to appropriate folder
                    email.IsRead = true;
                    email.Update(ConflictResolutionMode.AutoResolve);
                    if (isDebugEnabled)
                    {
                        logger.Debug("MARKED AS READ...");
                    }
                    email.Move(caseCreatedMailFolder.Id);
                    if (isDebugEnabled)
                    {
                        logger.Debug("MOVED TO FOLDER...");
                    }

                    emailData = null;
                }
                catch (Exception e)
                {
                    email.IsRead = true;
                    email.Update(ConflictResolutionMode.AutoResolve);
                    if (isErrorEnabled)
                    {
                        logger.Error("Failed to Process...MARKED AS READ..." + Environment.NewLine
                            + emailData.ToString() + Environment.NewLine + e.InnerException.StackTrace);
                    }
                }

                emailData = null;
                emailCount++;
            }
        }

How to send email from a trigger when a record is inserted

Setting up a trigger using sp_send_dbmail is easier than you think. If you have set up your SQL server to send out emails then it's matter of setting up your email template in the trigger. Have a look at below trigger i have created. 

In this trigger i have first create email body querying from a database query. Then i embed it and send to the recipient that is again queried out from the DB. 

I believe this trigger will save your time :)

USE [Database Name]

GO

/****** Object:  Trigger [dbo].[NotificationEmail]  ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [dbo].[NotificationEmail]

ON [dbo].[logEvents]

AFTER INSERT 

AS

--Initiation

declare @email varchar(200), @ID varchar(12), @emailBody varchar(500), @emailSubject varchar(150)

--Select email and note

select @email = A.EMAIL, @ID = A.ID

from <DB TABLE> A

      

--Create email strings

set @emailBody = 'A new note has been added to ' + @ID + ', which has triggered an email to ' + @email 

+ char(13) + char(10) + char(10) + 

+'As this ID has been assigned to you, please review the updated information.'+char(10) + char(10) 

+'Thank You'

set @emailSubject = 'New note added to ' + @ID

if @email is not null

begin

--Send email

      EXEC msdb.dbo.sp_send_dbmail

            @profile_name = 'emails',

            @recipients = @email,

            --@recipients = 'sarasidias@test.com',

            @body = @emailBody,

            @subject = @emailSubject

end

C#: Read from a spreadsheet/Excel

In my previous post i have mentioned about reading from CSV. In this i'm going to share the method i use to read from excel.

Reference:

using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;

Method:

        public void ReadExcel(string excelPath)
        {
        private static Microsoft.Office.Interop.Excel.Application appExcel;
        Microsoft.Office.Interop.Excel.Workbook theWorkbook;
        Microsoft.Office.Interop.Excel.Sheets objSheets;
        Microsoft.Office.Interop.Excel.Worksheet objWorkSheet;
        Microsoft.Office.Interop.Excel.Range range;
        string[] strArray;

            try
            {
                appExcel = new Microsoft.Office.Interop.Excel.Application();
                if (appExcel != null)
                {
                    theWorkbook = appExcel.Workbooks.Open(excelPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    objSheets = theWorkbook.Worksheets;
                    objWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)objSheets.get_Item(1);
                    for (int i = 2; i <= 11; i++)
                    {
                        range = objWorkSheet.get_Range("A" + i.ToString(), "I" + i.ToString());
                        System.Array myValues = (System.Array)range.Cells.get_Value(Type.Missing);
                        strArray = myValues.OfType<object>().Select(o => o.ToString()).ToArray();

                        StartSearching(strArray);
                    }
                }
                else
                {
                    //clean up stuffs
                    theWorkbook.Close(false, Type.Missing, Type.Missing);
                    Marshal.ReleaseComObject(theWorkbook);

                    appExcel.Quit();
                    Marshal.FinalReleaseComObject(appExcel);
                }
            }
            catch (Exception ex)
            {
                _log.ErrorFormat("There was an error reported Read Excel! {0} \n\n", ex.Message, ex.StackTrace);
            }
            _log.DebugFormat("ReadExcel Completed");
        }

C#: Read from CSV

I'm writing this post thinking how easy it is to read and write to a file for a developer, however if you happen to write a piece of code to read from CSV i bet you take little longer. This is what happened to me. So i thought it's a good move to add this to my blog so i can refer this method anytime. 

If you think this helps you, make use of it whenever you want it.

Reference:

using System.IO;

Method to read from CSV: 

 public string[,] readCSV(string filePath)

        {

            try

            {

                string fileData = System.IO.File.ReadAllText(filePath);

                // Split into lines.

                fileData = fileData.Replace('\n', '\r');

                string[] lines = fileData.Split(new Char[] { '\r' }, StringSplitOptions.RemoveEmptyEntries);

                // See how many rows and columns there are.

                int totalRows = lines.Length;

                int totalCols = lines[0].Split(',').Length;

                // Allocate the data array.

                string[,] resultVals = new string[totalRows, totalCols];

                //populate the array with data

                for (int row = 0; row < totalRows; row++)

                {

                    string[] line_r = lines[row].Split(',');

                    for (int col = 0; col < totalCols; col++)

                    {

                        resultVals[row, col] = line_r[col];

                    }

                }

                return resultVals;

            }

            catch (Exception ex)

            {

                _log.ErrorFormat("There was an error reported! {0} \n\n", ex.Message, ex.StackTrace);

                return null;

            }

        }

Method that use the string array:

 private void StartSearching(string[,] strArray)

        {

            try

            {

                for (int i = 1; i < strArray.GetLength(0); i++)

                {

 

                        Narnum = strArray[i,0].ToString().TrimEnd().ToLower();

                        EmpName = strArray[i,1].ToString().Trim();

                        PreferredName = strArray[i,2].ToString().TrimEnd();

                        Firstname = strArray[i,3].ToString().TrimEnd();

                        Surname = strArray[i,4].ToString().TrimEnd();

                        Position = strArray[i,5].ToString().TrimEnd();

                        Department = strArray[i,6].ToString().TrimEnd();

                        Manager = strArray[i,7].ToString().TrimEnd();

                        Division = strArray[i,8].ToString().TrimEnd();

     Validate(Narnum, EmpName, Position, PreferredName, Surname, Firstname, Manager, Division);

                }

            }

            catch (Exception ex)

            {

                _log.ErrorFormat("There was an error reported! {0} \n\n", ex.Message, ex.StackTrace);

            }

        }

C#: Convert names to proper case

I have come across situations where some names or surnames have a character and managing casing is the important task. So i developed a simple method to convert the names to proper case in exceptional cases.

Ex: O'Brien , Tucky-Knight, Van Van, Nra (Nanette)

My method below identify the special character and make the next letter to upper case.


public string ConvertToProperNameCase(string input)
{
    char[] chars = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(input.ToLower()).ToCharArray();

             for (int i = 0; i + 1 < chars.Length; i++)
             {
                 if ((chars[i].Equals('\'')) || (chars[i].Equals('-')) || (chars[i].Equals('(')) || (chars[i].Equals(' ')))
                 {
                     chars[i + 1] = Char.ToUpper(chars[i + 1]);
                 }
             }
    return new string(chars);
}