SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


PowerBI Desktop - CALENDAR() function


PowerBI Desktop - CALENDAR() function

Author
Message
pietlinden
pietlinden
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42350 Visits: 16083
I have a working PBIX file that looks at symptom data over time, and I was trying to do the whole thing inside of PowerBI Desktop, without using SQL Server (heresy, I know!), just to see if it could be done. The original data I received is in Excel (I have 2013), and I can do everything I need to except generate a calendar table. If I use SQL Server, I can just call a function that generates the Calendar for me... I just pass in the startDate and the number of Days I want the calendar to span and I'm off to the races.

When I try to create the Calendar using the CALENDAR() function from this link, I get
"Expression error: The name 'CALENDAR' wasn't recognized. Make sure it's spelled correctly."
I have product version 2.32.4307.641, if it matters. Does the function not work with PowerBI?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)

Group: General Forum Members
Points: 680773 Visits: 45603
pietlinden (3/12/2016)
I have a working PBIX file that looks at symptom data over time, and I was trying to do the whole thing inside of PowerBI Desktop, without using SQL Server (heresy, I know!), just to see if it could be done. The original data I received is in Excel (I have 2013), and I can do everything I need to except generate a calendar table. If I use SQL Server, I can just call a function that generates the Calendar for me... I just pass in the startDate and the number of Days I want the calendar to span and I'm off to the races.

When I try to create the Calendar using the CALENDAR() function from this link, I get
"Expression error: The name 'CALENDAR' wasn't recognized. Make sure it's spelled correctly."
I have product version 2.32.4307.641, if it matters. Does the function not work with PowerBI?



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




--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)

Group: General Forum Members
Points: 125433 Visits: 21783
This should work, I have models using this so I wonder why it doesn't work in your case, your version certainly supports it.
Cool
BTW: I normally use a similar function as Jeff because it is supported in wider range of products.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)

Group: General Forum Members
Points: 680773 Visits: 45603
Eirikur Eiriksson (3/13/2016)
This should work, I have models using this so I wonder why it doesn't work in your case, your version certainly supports it.
Cool
BTW: I normally use a similar function as Jeff because it is supported in wider range of products.


Could it be that someone simply didn't enable or maybe didn't install SSAS components?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pietlinden
pietlinden
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42350 Visits: 16083
That someone would be me =).

No, I didn't install SSAS. I was just trying to see how much I could do in PowerPivot without using T-SQL. When I did the whole thing with T-SQL, I used (I think) a calendar table function from here, and since i could specify the start and end dates, I had no excess dates... Was just trying to get my head around M, but that might not be worth the hassle.
pietlinden
pietlinden
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42350 Visits: 16083
found an explanation for any other intrepid folks that might be trying to create a calendar using PowerQuery... (not sure why one would want to do that, since it's so easy to use a script here... but anyway. Maybe because you don't have access to one?)

There's an explanation in PowerPivot Alchemy on page 197 that is missing from Rob Collie and Avichal Singh's book. Collie's book has some code, but the really confusing thing is that he uses Date as a variable name and as a datatype. If you read the explanation in Power Pivot Alchemy, they put some of the script in italics so that you can see what values to change. (he admits to being something of an M script kiddie, which is okay... a little bit of explanation of what's going on would have been a lot more helpful... otherwise, really good book!)

Hope this helps someone else... just a really confusing chunk of code for me.
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)SSC Guru (125K reputation)

Group: General Forum Members
Points: 125433 Visits: 21783
Are you using it as a measure/calculate table?
Cool
pietlinden
pietlinden
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42350 Visits: 16083
No, I'm using the Calendar table as a Dimension so I can lump sales etc into "bins" -- summarize by Year/Month whatever.

I was using a dataset someone gave me that was about heart-related symptoms over time. The basic structure of the table was

CREATE TABLE PatientData (
PatientID INT,
SymptomName VARCHAR(20),
SeverityLevel TINYINT,
ConsentDate DATE );



I originally used someone's code from here (Todd Fifield's, I think) that created a Calendar table... I basically did something like this:

DECLARE @MinDate DATE
, @MaxDate DATE
, @DayCount INT;

SELECT @MinDate = MIN(Consent_Date)
, @MaxDate = MAX(Consent_Date);

SET @DayCount = DATEDIFF(day,@MinDate,@MaxDate) + 1;



and then I passed @MinDate and @DayCount to the function that returns a Calendar table. (with Monthname, dayOfWeek, etc...)

I was just trying to see how hard it would be to create a calendar table like that on the fly in M. In a word, PITA. Might be worth it for intellectual exercise, but otherwise, just a hassle. =(
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)SSC Guru (680K reputation)

Group: General Forum Members
Points: 680773 Visits: 45603
pietlinden (3/13/2016)
No, I'm using the Calendar table as a Dimension so I can lump sales etc into "bins" -- summarize by Year/Month whatever.


While that works and can be fairly well effective, I find that a totally non-intuitive and somewhat unorthodox method for lumping report items together works very well without giving up much in the line of flexibility. Rather than trying to do your binning by joining, I've found that it's much more effective to add persisted computed columns to the source table with the formula to calculate the 1st day of the month, week, quarter, or year. It's a bit like using a materialized (indexed) view but without that little bit of heartache. And, those columns can be indexed! In fact they can be indexed to include other columns so that a trip to the table isn't even necessary until after the aggregation is complete and you just need to add some "labels" to it. An added benefit is that since the indexes would have the temporally based columns as the leading column, they would rarely require any defragmentation.

Of course, since monthly sales don't change after they've been entered, perhaps a preaggregated table containing older aggregations unioned with a query of current data would also speed up the process.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
pietlinden
pietlinden
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42350 Visits: 16083
Jeff,

that's basically what I did. (it's like a mini star schema, and Calendar is just a dimension table). Turns out, I used your calendar table-valued function. =) ... so I have a bunch of stuff in the table that's persisted so that it's easy to report on.

The interesting part was that PowerBI let me declare some variables, get the earliest and latest dates in my table, and generate the Calendar on the fly... but creating the table in M was waaay more than I wanted to do. I did read Chris Webb's blog and some other stuff, but seems like a huge hassle... Might try it again when I get my head around M a little better.

Pieter
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search