That particular Calendar() function is a DAX function available in SSAS (Analysis Services) and, I could be wrong, but I don't believe it will work with PowerBI.

This is what I'll sometimes use. I don't remember when I first created it. Other times, I may use a Calendar Table. A lot of folks use similar functions. And, no, I don't know if this works with PowerBI, either.

As usual, details and usage examples are in the comments.

CREATE FUNCTION dbo.Dates

/**********************************************************************************************************************

Purpose:

Given a start and end date, create all dates between them inclusively.

Usage Examples:

--===== Basic Syntax

SELECT Date FROM dbo.Dates(@pStartDate,@pEndDate)

;

--===== All Dates for Year 2016

SELECT Date FROM dbo.Dates('20160101','20161231')

;

--===== All Dates from 1900-01-01 thru 2200-01-01

SELECT Date FROM dbo.Dates('1900','2200')

;

Revision History:

Rev 00 - Date Unknown - Jeff Moden - Initial Creation and Unit Test

**********************************************************************************************************************/

(

@pStartDate DATE

,@pEndDate DATE

)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN WITH

E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))v(N)) --=== 10E01 or up to 10 Rows

, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --=== 10E04 or up to 10,000 Rows

,E16(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c, E4 d) --=== 10E16 or more rows than you'll ever need

SELECT Date = @pStartDate

UNION ALL

SELECT TOP (DATEDIFF(dd,@pStartDate,@pEndDate))

Date = DATEADD(dd,CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS INT),@pStartDate)

FROM E16

;

GO