Browse 545 attributed reviews, viewable separately for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

Changes to Power BI Desktop for August 2021
Part five of a five-part series of blogs

This month sees individually customisable shapes and further refinements to the X axis constant line, among other improvements to Power BI Desktop.

1. Changes to Power BI Desktop for August 2021
2. You can now tweak the properties of individual shapes
3. More options for the X axis constant line
4. Sharing Q and A synonyms
5. A New Date/Time Format for DAX (this blog)

We've been creating our idiosyncratic monthly blogs on Power BI updates since November 2016, and also deliver online and classroom Power BI courses.

Posted by Andy Brown on 31 August 2021

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# A New Date/Time Format for DAX

Suppose that you want to report sales before and after an important milestone in British life:

Prince Louis Arthur Charles was born at 11:01am on 23rd April 2018.

Here's a measure which would give the first statistic above, using the DATE and TIME functions to get the date and time:

Before Prince Louis = CALCULATE(

SUM(Purchase[Quantity]),

// sale occurred before 11:01 on 23rd April 2018

Purchase[PurchaseDateTime] <= (DATE(2018,4,23) + TIME(11,1,0))

)

Someone at Microsoft obviously felt this was a bit clunky, and introduced the following new date/time formats:

To show Use
Just a date dt"YYYY-MM-DD"
A date and time dt"YYYY-MM-DDThh:mm:ss"
dt"YYYY-MM-DD hh:mm:ss"

So our measure above could be written:

Before Prince Louis = CALCULATE(

SUM(Purchase[Quantity]),

// sale occurred before 11:01 on 23rd April 2018

Purchase[PurchaseDateTime] <= dt"2018-4-23 11:1:0"

)

Notice how you can include just one digit for parts of the date and time where appropriate (so there's no need to type 04 in for April, for example).