SSRS - Order By Day Of The Week

In SQL Reporting Services there is no out of the box way of order a report or chart by the days of the week. If you try to order by the name you end up with the days alphabetically sorted which is not very helpful.

One of the ways around this is to click use a switch statement in the Sort By section.

=SWITCH(Fields!DateNamePart.Value = "Monday", 1, Fields!DateNamePart.Value = "Tuesday", 2, Fields!DateNamePart.Value = "Wednesday", 3, Fields!DateNamePart.Value = "Thursday", 4, Fields!DateNamePart.Value = "Friday", 5, Fields!DateNamePart.Value = "Saturday", 6, Fields!DateNamePart.Value = "Sunday", 7)

After you have added this the days are now sorted in order and the data now has some visual value and it is much easier to spot when the trend in the chart.

DAX for DATEADD(DD, -1, GETDATE())

In DAX the equivalent of GETDATE() is NOW().


The DATEADD function varies depending on what time unit you are adding. To add or subtract days it couldn't be easier.


NOW() -1 takes a day from the current time. NOW() + 5 adds five days.


To add a month you use the EDATE function. So to add two months to the current date you use EDATE(NOW(), 2).


So to use DATEADD(DD, -1, GETDATE()) in a filter or calculate table you would do something like.


CALCULATETABLE('Contacts','Contacts'[CreatedOn] > now() -1 )