How to display Start and End Dates of Week and Month in Bold BI

In certain scenarios, it may be more beneficial to display the start and end dates of a week or month, rather than the week number or month name. This can be achieved in Bold BI using specific expressions.

Week Start and End Dates

To display the start and end dates of a week, the following expression can be used:

CONVERT(nvarchar(30), DATEADD(dd, -(DATEPART(dw, [DateColumn])-1), [DateColumn]) ,110) + '-'+CONVERT(nvarchar(30),DATEADD(dd, 7-(DATEPART(dw, [DateColumn])), [DateColumn]),110)

This expression is created for the desired date column and bind in the pivot grid.

Month Start and End Dates

Similarly, to display the start and end dates of a month, the following expression can be used:

CONVERT(nvarchar(30), DATEADD(mm, DATEDIFF(mm, 0, [DateColumn] ) ,0), 110) + '-' +
CONVERT(nvarchar(30),DATEADD(SECOND, -1, DATEADD(mm, 1,  DATEADD(mm, DATEDIFF(mm, 0, [DateColumn]) , 0) ) ), 110)

This expression is also used on the pivot grid widget.

By using these expressions, users can easily view the start and end dates of a week or month in Bold BI.

References

• SQL Server DATEADD() Function
• SQL Server DATEDIFF() Function
• SQL Server CONVERT() Function