Forum Discussion
JLaine
8 years agoHelper I
Help needed graphing a count between a date range
I am new to using Power BI, and am looking for some guidance with the following: I have a large SQL data set (larger than I can work with in Excel) contianing many data columns including the foll...
JLaine
8 years agoHelper I
Not fully, no. This is still an open item.
dipique
8 years agoNew Member
I was able to figure this out, details are on this thread.
First, I created a calendar that contains only the first day of each month, and only dates that current or in the past:
Program Active Months = FILTER(CALENDAR(DATE(2018,1,1),DATE(2025,12,31)),AND(DAY([Date])=1, TODAY()>=[Date]))
Then, I used this to produce the table I wanted which showed me enrollee count by client and month:
Active Enrollees =
VAR tmpTable =
SELECTCOLUMNS(
FILTER(
GENERATE(
Enrollment,
'Program Active Months'
),
[Date] >= [Effective_Date__c] &&
[Date] <= Enrollment[End Date]
),
"Id", Enrollment[Id],
"Date", [Date],
"Account", [Account Name]
)
RETURN GROUPBY(tmpTable,[Date],[Account],"Count",COUNTX(CURRENTGROUP(),[Id]))I hope this helps anyone else in a similar situation.