I have created the following query SQL 2014 and successfully executed it:
select
CONVERT(CHAR(7),DATEADD(MONTH,DATEDIFF(MONTH,0,StartDate),0),120) as 'Month & Year',
Shift,
SUM(DownTime) as 'Month Total Down Time',
SUM(DownTime)+
COALESCE(
(
select SUM(s2.DownTime)
FROM LineData s2
where
s2.Shift = s.Shift
and s2.StartDate >= DATEADD(YEAR,DATEDIFF(YEAR,0,s.StartDate),0) --Verify the date is in the same year
and s2.StartDate < DATEADD(YEAR,1,DATEADD(YEAR,DATEDIFF(YEAR,0,s.StartDate),0))
and DATEADD(MONTH,DATEDIFF(MONTH,0,s.StartDate),0) > s2.StartDate --Verify the date s less than s.TDate
),0) as 'YTD Total Down Time'
from LineData s
group by
DATEADD(MONTH,DATEDIFF(MONTH,0,s.StartDate),0),
DATEADD(YEAR,1,DATEADD(YEAR,DATEDIFF(YEAR,0,s.StartDate),0)),
DATEADD(YEAR,DATEDIFF(YEAR,0,s.StartDate),0),
Shift
ORDER BY
'Month & Year',
Shift
When I attempt to use the query in a direct query import I receive the following error:
PowerBI error: Microsoft SQL: The Order by clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless top or for xml is also specified in PowerBI
However, if I open Power Query Editor I see my query with the correct data that the query produces. Unfortunately, if I attempt to apply the query I receive this error:
Failed to save modifications to the server. Error returned: 'Partitions that contain a full dataset (not sampled data) have to be configured for DirectQuery and Full DataViews, or use Import query mode to retrieve data. This model appears to have partitions that are non-compliant. Please check partition configuration.'
Can someone please explain why Power BI can not run a query that SQL can? And how can I resolve the issue?
Thanks in advance.
Mark
