Aggregating records with a date column to week, month, and quarter with SQL Server DirectConnect

[intrasight]

Is this possible? Is there a standard M pattern for this? I didn't find anything here or Googling, but perhaps I'm asking the question the wrong way. I have daily data and I wish to group/aggregate by week or month or quarter. I was assuming that this would be easy in Power BI using M, but since I've found no samples, I am thinking perhaps not.

[intrasight]

I just tried adding this expression:

#"AddMonth" = Table.AddColumn(dbo_InventoryView, "Month", each Date.StartOfMonth([StorageDate]), type date)

But I get the error:

"This step results in a query that is not supported in DirectQuery mode"

[Eric_Zhang · Accepted solution]

@intrasight

The errors is due to the limitation of DirectQuery mode. Since you're using SQL Server, the requirement can be easily achieved by queries as below. You can apply those queries individually when "get data".

select year(orderdate)[year],datepart(week,orderdate) [week],sum(sales) totalSales from orders
group by year(orderdate),datepart(week,orderdate)

select year(orderdate) [year],datepart(quarter,orderdate) [quarter],sum(sales) totalSales  from orders
group by year(orderdate),datepart(quarter,orderdate)

select dateadd(month,datediff(month,0,orderdate),0) [month] ,sum(sales) totalSales from orders
group by dateadd(month,datediff(month,0,orderdate),0)