When dealing with incomplete date sets in Power BI, I often add in a quick and dirty Data table to join to the data set(s) for use in filters. The code below does a wonderful job of this with a simple copy/paste.

Date Table

Date =
    CALENDAR (DATE(YEAR(NOW())-1,1,1), 
    "Date as Integer", FORMAT ( [Date], "YYYYMMDD" ),
    "Year", YEAR ( [Date] ),
    "Month Number", FORMAT ( [Date], "MM" ),
    "Year Month Number", FORMAT ( [Date], "YYYY/MM" ),
    "Year Month Short", FORMAT ( [Date], "YYYY/mmm" ),
    "Month Name Short", FORMAT ( [Date], "mmm" ),
    "Month Name Long", FORMAT ( [Date], "mmmm" ),
    "Day of Week Number", WEEKDAY ( [Date] ),
    "Day of Week", FORMAT ( [Date], "dddd" ),
    "Day of Week Short", FORMAT ( [Date], "ddd" ),
    "Quarter", "Q" & FORMAT ( [Date], "Q" ),
    "Year Quarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ),
    "Week Number", WEEKNUM([Date], 1 )

Next, we’ll take things further by adding in some calculated columns and measures for some of the most used functions.

StartOfYear / EndOfYear



StartOfQuarter / EndOfQuarter



StartOfMonth / EndOfMonth



StartOfWeek / EndOfWeek

Since there is no “out of box” solution for this. You can achieve the Start/End of week my using a min/max in conjunction with the “Week Number” column as a Slicer. I must admit, this is a sloppy hack.


ENDOFWEEK = MAX('Date'[Date])

Current Week

Current Week = CALCULATE ( VALUES ('Date'[Week Number] ), 'Date'[Date] = TODAY() )


December 4th, 2019

SQL Puzzle

In my endless search for the end of the internet, I stumbled across a fun SQL Puzzle.  Link:  

October 25th, 2019

New Power BI Calendar Table

While the old one still works, it doesn’t exactly follow Power BI and DAX best practices. Much like everything in […]

March 5th, 2019

Power BI Report Last Data Refresh Date & Time Tile

With all the data flowing within companies, we needed a way to track the last time Power BI reports were […]

December 3rd, 2018

Date Table and Extras for Power BI

When dealing with incomplete date sets in Power BI, I often add in a quick and dirty Data table to […]

February 14th, 2018

Invincible workflows

Today I encountered a list item that had two instances of the same workflow, both stuck on the first step. […]

May 17th, 2017

The case of the missing App Launcher

Today I deleted and recreated a SharePoint 2016 Web application and recreated it, without issue. Well, except that the new […]

March 10th, 2017

The server failed to process the request. Aka, the SharePoint version of PCLoadLetter

It’s Friday so I’ll keep this short The ongoing power struggle between SharePoint and it’s tamers continues. Today we’re faces […]

March 3rd, 2017

Search, search, search, all the day long…

So, I just rolled SharePoint 2016 into a test environment and launch Central Admin, and the first thing my eyes […]

May 5th, 2016

Oh, happy day!

SharePoint 2016 has been released to MSDN! Time to practice different upgrade strategies and see what this baby can do! […]

March 29th, 2016

Branding Gone Wild

Today I ran into a problematic sub-site in my SharePoint 2013 farm. This sub-site, named “Test”, was where I had […]