August 12, 2019 at 4:12 am
Hello, I am having an issue getting an accurate count of records between two dates, actually start of week dates grouped by a trainer and the week start date.
The first, I need to get the count of records 6 weeks prior to the dates week start date.
The second is to get the count of records 12 weeks prior to the dates week start date.
This is so I can represent the averages on a line graph in PowerBi.
What I have so far is this;
SELECT [Student: Trainer]
, DATEADD(wk, DATEDIFF(wk,0,[Student Unit Status: End Date] ), 0) as [WeekStart]
,(SELECT COUNT([Student Unit Status: Code])
FROM [CTISAM].[dbo].[upload_units] a1
INNER JOIN upload_students b1 ON a1.[Student: Party ID] = b1.[Party: Party ID]
where [Student Unit Status: Outcome] = 'Competent'
and b1.[Student: Trainer] = b.[Student: Trainer]
AND a1.[Student Unit Status: End Date] Between DATEADD(wk, -6, DATEADD(wk, DATEDIFF(wk,0,a1.[Student Unit Status: End Date] ), 0)) AND DATEADD(wk, DATEDIFF(wk,0,a1.[Student Unit Status: End Date] ), 0)
AND DATEADD(wk, DATEDIFF(wk,0,a1.[Student Unit Status: End Date] ), 0) = DATEADD(wk, DATEDIFF(wk,0,a.[Student Unit Status: End Date] ), 0)
) as [6WeekAve]
,(SELECT COUNT([Student Unit Status: Code])
FROM [CTISAM].[dbo].[upload_units] aa
INNER JOIN upload_students bb ON aa.[Student: Party ID] = bb.[Party: Party ID]
where [Student Unit Status: Outcome] = 'Competent'
and bb.[Student: Trainer] = b.[Student: Trainer]
AND [Student Unit Status: End Date] Between DATEADD(wk, -12, DATEADD(wk, DATEDIFF(wk,0,aa.[Student Unit Status: End Date] ), 0)) AND DATEADD(wk, DATEDIFF(wk,0,a.[Student Unit Status: End Date] ), 0)
AND DATEADD(wk, DATEDIFF(wk,0,aa.[Student Unit Status: End Date] ), 0) = DATEADD(wk, DATEDIFF(wk,0,a.[Student Unit Status: End Date] ), 0)
)as [12WeekAve]
FROM [dbo].[upload_units] a
INNER JOIN upload_students b ON a.[Student: Party ID] = b.[Party: Party ID]
where [Student Unit Status: Outcome] = 'Competent'
GROUP BY b.[Student: Trainer], DATEADD(wk, DATEDIFF(wk,0,[Student Unit Status: End Date] ), 0)
I get the same values for both nested queries. I have also tried with a calendar table, with the same result. Any help would be appreciated.
August 13, 2019 at 2:01 am
If you're doing this in PowerBI, don't you want a measure that calculates a count, like COUNTROWS('FactTable') and then use CALCULATE() to modify the evaluation context?
Here's an example of doing something like what you are talking about (It's a super contrived example, but you could expand the evaluation context by extending the dates you're looking at by modifying the start and end dates in the evaluation context... That's what the DATESBETWEEN(…) is doing.:
(Sorry, no, that's not SQL, that's DAX... enormous difference.)
Count PrevMonth and Next Yr = CALCULATE(
[Symptom Count],
ALL('Calendar'),
DATESBETWEEN('Calendar'[Date],
DATEADD(FIRSTDATE('Calendar'[Date]),-1,MONTH),
DATEADD(LASTDATE('Calendar'[Date]),1,YEAR)
)
)
DAX is a completely different animal than T-SQL Maybe have a look at http://www.powerpivotpro.com That's the gentlest introduction to DAX I know of. http://www.daxpatterns.com is Ferrari & Russo's site. That's super handy too...
I didn't completely forget about all of your filters - I just thought adding all of them at once would be way too mind-boggling. You can add more if you put a comma after the second-to-last close paren...
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply