Creating a DAX Calculated Column for Today, Yesterday and Next Working Day with Variables

Below was a question from the Power BI Community where the output was to have a calculated column in the Date Table, which would have Today for Todays date, Yesterday for Yesterdays Date, and Next Working Day (Being the following Monday)

The challenge was that for the “Next Working Day” it should only be for the next Monday and not all Mondays.

If you did not know you can use DAX Variables not only in DAX measures, but also in DAX Calculated columns.

I would like to mention that I had always like to step out my calculations when working in long or complex DAX measures or calculated columns. This video from Guy In a Cube with Patrick and Marco gives a great introduction on how to Debug DAX, in which they demonstrate how to use variables in DAX measures to debug the measures.

So below is my DAX calculated column with the explanation afterwards.

T/Y/Tom =
VAR TodaysDate =
    TODAY ()
VAR YesterdayDate =
    TODAY () - 1
VAR NextMondaysDate =
    SWITCH (
        TRUE (),
        'Date'[Date] - WEEKDAY ( 'Date'[Date], 2 )
            + 1
            >= TodaysDate, 'Date'[Date] - WEEKDAY ( 'Date'[Date], 2 )
            + 1,
        TODAY () - 9
    )
VAR NextWorkingDay =
    SWITCH (
        TRUE (),
        'Date'[Date] = TodaysDate, "Today",
        'Date'[Date] = YesterdayDate, "Yesterday",
        'Date'[Date] = NextMondaysDate, "Next Working Day",
        "Not Applicable"
    )
RETURN
    NextWorkingDay

Code above formatted with the DAX Formatter

  • Line 1 – This is where I am giving my Calculated Column a name called T/Y/Tom meaning Today/Yesterday/Tomorrow
  • Lines 2 & 3 – This is where I am creating a variable TodaysDate which today’s date using the DAX function TODAY()
    • With today’s date being 02 July 2018
  • Lines 3 & 4 – This is where I am creating a variable called YesterdayDate getting yesterday’s date, going back one day from today’s date.
  • Lines 6-8 is where I am creating a variable called NextMondaysDate.
    • I am then using the DAX SWITCH function which I find personally is a lot easier to use than an IF function.
    • My experience has also taught me that often an initial requirement is for two possible conditions, but once it is looked at more closely there are more than two conditions. And using the combination of the SWITCH and TRUE function makes it a lot easier to add in as many conditions are required.
  • Lines 9 & 10 – This line is quite interesting, because what I wanted to do was to find what Next Mondays date would be.
    • And if I had to put this single calculated column into my dataset I would get the following as shown below.
    • I actually did this as part of my debugging process to ensure that I was getting the right date.
    • As you can see above what this does is it gives me the date for each Monday in each Week.
      • For this current week the date for Monday is 07/02/2018
      • Whilst for next week the date for Monday is 07/09/2018
  • Lines 11 & 12 is where I am comparing to see if it is greater than equal to todays date.
    • If it is then use the Mondays Date.
    • In this example the output would appear to be the same as the DAX in lines 9 & 10.
    • The difference here is if you had to use it outside of the SWITCH context it would then result in every Monday having the “Next Working Day” which is not the required result.
  • Lines 13 & 14 – This is where I am putting in the ELSE condition which is set to take Today’s date and go back 99 999 days. Which should not be seen in the dataset.
  • Lines 15 – 22 – This is where once again I am using the SWITCH and TRUE DAX functions, because I have got multiple conditions to evaluate.
    • The first condition is where I am looking at my date table and where my calculated DAX column matches my variable for today.
      • If you had to not imagine it, but see what it would look like, it would look like the following:
        • ‘Date'[Date] = “07/02/2018”, “Today”,
    • The second condition is where I am looking at my date table and where my calculated DAX column matches my variable for yesterday.
      • If you had to not imagine it, but see what it would look like, it would look like the following:
        • ‘Date'[Date] = “07/01/2018”, “Yesterday”,
    • The third condition is where I am looking at my date table and where my calculated DAX column matches my variable for next Monday.
      • If you had to not imagine it, but see what it would look like, it would look like the following:
        • ‘Date'[Date] = “07/09/2018”, “Next Working Day”,
  • Lines 23 & 24 – This is where I close off my variables and use my final variable called NextWorkingDay

So now when I look at my date table I see the following below which what is I wanted to get.

As you can see above there is a previous Monday’s date, and it says Not Applicable, so I am getting the results I expect.

I hope you found this blog post useful on how to use DAX variables in DAX calculated columns, as well as how to use it for debugging your DAX code.

As always if you have got any questions or suggestions please leave them in the comments section below.

Power BI – Why creating columns in the Query Editor is better, quicker and more efficient than creating Calculated Columns.

I have been reading and helping out where I can on the Power BI Community forums. And what I often see is people trying to use the Calculated Columns in the Power BI Desktop Model.

So in this blog post below I am going to explain why it is better, quicker and more efficient to use the Query Editor to create additional columns.

Example Scenario

For this example, what I am going to be doing is searching within the Description
column for a piece of text, and then creating a new column which specifies the area.

The two pieces of text that I want to find are “Gold Coast” and “Brisbane“, which will then go into my new column called Area.

If I cannot find “Gold Coast” or “Brisbane” I will then make it “Unknown

The harder way doing it with a Calculated Column

Below are the steps where you can do this using a calculated column in your Power BI Model.

  • To do this I opened up my Power BI Desktop file and then in the columns clicked on New Column
  • Now in order to find the piece of text that I am looking for I have to use the SEARCH
    function in DAX, but as you can see below in order for this to work I have to add in some additional DAX functions

    Area (Model) =

     IF (

        IFERROR ( SEARCH ( “*Gold Coast*”, [Description] ), -1 ) = 1,

        “Gold Coast”,

    IF (

    IFERROR ( SEARCH ( “*Brisbane*”, [Description] ), -1 ) = 1,

    “Brisbane”,

    “Unknown”

    )

    )

  • As you can see above I have had to use the IFERROR, so that if there is indeed an error I can capture this, and not cause the calculated column to fail.
  • As well as the outer IF condition is where I am saying if the SEARCH = 1 meaning that it is TRUE or Correct, then what Text I want it to display.
  • And as with my two conditions this is for the “Gold Coast” and “Brisbane”
  • And then finally if either of the conditions are not met, then make the output “Unknown
  • NOTE: There might be a more efficient way to do this, and if there is please leave it in the comments section.

As you can see with the above method, this is very clunky as well as very prone to errors.

As well as in the past when I had quite a few conditions I was searching for, it becomes very long and complex.

The easier way, using the Query Editor

Below I am going to show how to achieve the same result in a much easier way using the Query Editor.

  • To do this I opened up my Power BI Desktop file and clicked on Edit Queries
  • Now once the Query Editor Opened I clicked on Add Column in the Ribbon.
  • Next I selected Conditional Column and create it as shown below.
  • As you can see above this is searching for my requirements within the Description column.
  • I then clicked Ok, and then clicked Close And Apply to Load the data into my Power BI Desktop Model.

As you can see to create the conditional column is very easy and simple to search for text and create the additional column.

As well as if there is a requirement for additional conditions, it is very easy to add.

You can see below that both columns have the same output.

Conclusion

So in conclusion you can see that there is a simple way, as well as a more complex way to get the same desired output.

I would also like to note that with my current understanding of Power BI and how the compression and performance works, that it is best practice to ideally create your columns in your source data. If you cannot it is then next best to do it within the Query Editor. And then last resort is in the Power BI Model.

And with each process above being less optimal.

And in my opinion I do not see why you would need to create a Calculated Column, when this can very easily be achieved in the Query Editor.

You can download the sample file here: Calculated Column.pbix