Include individual if not in previous 2 months

jmwdba · February 18, 2020, 1:50pm

So what I am trying to accomplish in the below is if their ID is in January and the status is either “Enrolled in Future Term” or “Enrolled in Term” then I don’t want to see them included again for 2 months after that (a term runs 3 months). So if they are are in January by their ID having a January date then they should be excluded in the monthly counts until April. I am unsure of how to write the DAX to accomplish this.

I have attached an excel file that is a sampling of the data fields, I am using a standard dates table the one provided by sqlbi just relabeling Fiscal Year to Academic Year.

Sample File.xlsx (9.1 KB)

The only measure I have created so far that is being used in the above visual is:

Eligible Students = CALCULATE(
DISTINCTCOUNT( Eligibility[Student ID]), VALUES(Dates[Academic Year]), VALUES(Eligibility[Term Description]), VALUES(Eligibility[Academic Plan] ))

So in the case of student ID # 123456 he is “Enrolled In Term” for January, which means I don’t want to count him in February or March. If his status is “Eligible Not Enrolled” for April then I want him to start being counted again and for every month thereafter that his status is “Eligible Not Enrolled.” a status is assigned to the student for every term based on whether they are in a course or not.

Melissa · February 18, 2020, 1:59pm

Hi @jmwdba,

There’s no excel file attached to your topic. Can you supply some data?
Thanks.

jmwdba · February 18, 2020, 2:13pm

Yikes sorry about that. I edited my submission and added the excel.

Melissa · February 18, 2020, 3:17pm

Hi @jmwdba,

Thanks for your prompt reply!
So I’ve added a standard Dates table and expanded that with a TermStart colum, like so:

`TermStartColumn = 
       Table.AddColumn(Source, "TermStart", each Number.Mod( [MonthOfYear]+2, 3 ), type number )`

.
And created this measure:

Student count = 
VAR TermStart = LOOKUPVALUE( Dates[TermStart], Dates[MonthName], SELECTEDVALUE(Dates[MonthName]))
VAR MonthOffset = LOOKUPVALUE( Dates[MonthOffset], Dates[MonthName], SELECTEDVALUE( Dates[MonthName] ))
VAR OffsetNumber = IF( TermStart >0, MonthOffset - TermStart, MonthOffset )

VAR StudentsEnrolledTermStart =
    CALCULATETABLE ( VALUES ( 'Enrolled data'[Student ID] ),
        FILTER ( ALL ( Dates ),
        Dates[MonthOffset] = OffsetNumber),
        FILTER( 'Enrolled data',
        'Enrolled data'[Enrollment Status] IN { "Enrolled in Future Term", "Enrolled in Term" }
        )
    )
VAR StudentsEnrolled =
    CALCULATETABLE ( VALUES ( 'Enrolled data'[Student ID] ),
        FILTER ( ALL ( Dates )|
        Dates[MonthOffset] = MonthOffset),
        FILTER( 'Enrolled data',
        'Enrolled data'[Enrollment Status] IN { "Enrolled in Future Term", "Enrolled in Term" }
        )
    )
RETURN
    COUNTROWS ( INTERSECT( StudentsEnrolled, StudentsEnrolledTermStart ) )

.
With this result. Now I had a realy small sample so can you validate this for me?

eDNA- Include individual if not in previous 2 months.pbix (103.7 KB)
.
ADDITION:
Changed the “Eligible Not Enrolled” value into “Enrolled in Future Term” and got this result.

BTW. We now only had one year of data, so splitting them out by MonthName is no problem but you should change this to Month+Year if your Dates table includes multiple years.

jmwdba · February 18, 2020, 3:44pm

I am trying to follow the:

TermStartColumn = Table.AddColumn(Source, "TermStart", each Number.Mod( [MonthOfYear]+2, 3 ), type number )

I don’t know if it matters but the first month of an academic year is July and the last month is June. So it goes July 2019, August 2019, September 2019, October 2019, November 2019, December 2019, January 2020, February 2020, March 2020, April 2020, May 2020, June 2020.

So my Dates table in M Language is built as follows:

let
Source = Query(#date(2000, 7, 1), #date(2030, 6, 30), 7),
#“Transform columns” = Table.TransformColumnTypes(Source, {{“AY”, type text}}),
#“Replace errors” = Table.ReplaceErrorValues(#“Transform columns”, {{“AY”, null}}),
#“Add Academic Year” = Table.AddColumn(#“Replace errors”, “Academic Year”, each if [AY] = “AY12” then “2011-12” else if [AY] = “AY13” then “2012-13” else if [AY] = “AY14” then “2013-14” else if [AY] = “AY15” then “2014-15” else if [AY] = “AY16” then “2015-16” else if [AY] = “AY17” then “2016-17” else if [AY] = “AY18” then “2017-18” else if [AY] = “AY19” then “2018-19” else if [AY] = “AY20” then “2019-20” else if [AY] = “AY21” then “2020-21” else if [AY] = “AY22” then “2021-22” else if [AY] = “AY23” then “2022-23” else if [AY] = “AY24” then “2023-24” else if [AY] = “AY25” then “2024-25” else if [AY] = “AY26” then “2025-26” else if [AY] = “AY27” then “2026-27” else if [AY] = “AY28” then “2027-28” else if [AY] = “AY29” then “2028-29” else if [AY] = “AY30” then “2029-30” else if [AY] = “AY11” then #date(2010, 11, 1) else if [AY] = “AY10” then #date(2009, 10, 1) else if [AY] = “AY09” then #date(2008, 9, 1) else if [AY] = “AY08” then #date(2007, 8, 1) else if [AY] = “AY07” then #date(2006, 7, 1) else if [AY] = “AY06” then #date(2005, 6, 1) else if [AY] = “AY05” then #date(2004, 5, 1) else if [AY] = “AY04” then #date(2003, 4, 1) else if [AY] = “AY03” then #date(2002, 3, 1) else if [AY] = “AY02” then #date(2001, 2, 1) else if [AY] = “AY01” then #date(2000, 1, 1) else “AY Definition Needed”),
#“Custom 1” = Table.AddColumn(#“Add Academic Year”, “Short Month”, each Text.Start([MonthName], 3), type text),
#“AY Month Sort” = Table.AddColumn(#“Custom 1”, “Month Sort”, each if [MonthName] = “July” then “1” else if [MonthName] = “August” then “2” else if [MonthName] = “September” then “3” else if [MonthName] = “October” then “4” else if [MonthName] = “November” then “5” else if [MonthName] = “December” then “6” else if [MonthName] = “January” then “7” else if [MonthName] = “February” then “8” else if [MonthName] = “March” then “9” else if [MonthName] = “April” then “10” else if [MonthName] = “May” then “11” else if [MonthName] = “June” then “12” else “0”),
#“Change column” = Table.TransformColumnTypes(#“AY Month Sort”, {{“Month Sort”, Int64.Type}}),
#“Transform columns 1” = Table.TransformColumnTypes(#“Change column”, {{“Academic Year”, type text}}),
#“Replace errors 1” = Table.ReplaceErrorValues(#“Transform columns 1”, {{“Academic Year”, null}}),
#“AY Quarter Number” = Table.AddColumn(#“Custom 1”, “AY Quarter Number”, each if [MonthName] = “July” then “1” else if [MonthName] = “August” then “1” else if [MonthName] = “September” then “1” else if [MonthName] = “October” then “2” else if [MonthName] = “November” then “2” else if [MonthName] = “December” then “2” else if [MonthName] = “January” then “3” else if [MonthName] = “February” then “3” else if [MonthName] = “March” then “3” else if [MonthName] = “April” then “4” else if [MonthName] = “May” then “4” else if [MonthName] = “June” then “4” else “0”),
#“Changed column type” = Table.TransformColumnTypes(#“AY Quarter Number”, {{“AY Quarter Number”, Int64.Type}}),
#“Transform columns 2” = Table.TransformColumnTypes(#“Changed column type”, {{“Academic Year”, type text}}),
#“Replace errors 2” = Table.ReplaceErrorValues(#“Transform columns 2”, {{“Academic Year”, null}}),
#“Inserted conditional column” = Table.AddColumn(#“Replace errors 2”, “Academic Quarter”, each if [AY Quarter Number] = 1 then “AQ1” else if [AY Quarter Number] = 2 then “AQ2” else if [AY Quarter Number] = 3 then “AQ3” else if [AY Quarter Number] = 4 then “AQ4” else “AQ0”),
#“Transform columns 3” = Table.TransformColumnTypes(#“Inserted conditional column”, {{“Academic Quarter”, type text}}),
#“Replace errors 3” = Table.ReplaceErrorValues(#“Transform columns 3”, {{“Academic Quarter”, null}}),
#“AY MonthSort” = Table.AddColumn(#“Replace errors 3”, “AY Month Sort”, each if [MonthName] = “July” then “1” else if [MonthName] = “August” then “2” else if [MonthName] = “September” then “3” else if [MonthName] = “October” then “4” else if [MonthName] = “November” then “5” else if [MonthName] = “December” then “6” else if [MonthName] = “January” then “7” else if [MonthName] = “February” then “8” else if [MonthName] = “March” then “9” else if [MonthName] = “April” then “10” else if [MonthName] = “May” then “11” else if [MonthName] = “June” then “12” else “0”),
#“Changed column type 1” = Table.TransformColumnTypes(#“AY MonthSort”, {{“AY Month Sort”, Int64.Type}, {“Date”, type date}}),
Custom = Table.AddColumn(#“Changed column type 1”, “Fiscal Year”, each if [AY] = “AY12” then “FY12” else if [AY] = “AY13” then “FY13” else if [AY] = “AY14” then “FY14” else if [AY] = “AY15” then “FY15” else if [AY] = “AY16” then “FY16” else if [AY] = “AY17” then “FY17” else if [AY] = “AY18” then “FY18” else if [AY] = “AY19” then “FY19” else if [AY] = “AY20” then “FY20” else if [AY] = “AY21” then “FY21” else if [AY] = “AY22” then “FY22” else if [AY] = “AY23” then “FY23” else if [AY] = “AY24” then “FY24” else if [AY] = “AY25” then “FY25” else if [AY] = “AY26” then “FY26” else if [AY] = “AY27” then “FY27” else if [AY] = “AY28” then “FY28” else if [AY] = “AY29” then “FY29” else if [AY] = “AY30” then “FY30” else if [AY] = “AY11” then “FY11” else if [AY] = “AY10” then “FY10” else if [AY] = “AY09” then “FY09” else if [AY] = “AY08” then “FY08” else if [AY] = “AY07” then “FY07” else if [AY] = “AY06” then “FY06” else if [AY] = “AY05” then “FY05” else if [AY] = “AY04” then “FY04” else if [AY] = “AY03” then “FY03” else if [AY] = “AY02” then “FY02” else “FY Definition Needed”),
#“Changed column type 2” = Table.TransformColumnTypes(Custom, {{“Fiscal Year”, type text}})
in
#“Changed column type 2”

Melissa · February 18, 2020, 3:56pm

It creates a sort of interval, just go to your Dates table and “add a custom column” enter this M code where [MonthOfYear] equals the Month number and change its type into a number:

Number.Mod( [MonthOfYear]+2, 3 )

You’ll see that each period start has the value of 0 and the 2 following months get 1 added.
Then we start over…

jmwdba · February 18, 2020, 4:16pm

I am now onto the formula but notice you have a MonthOffset. How are you deriving this and is there a specific value I should use based on my specific dates table like the MonthOfYear?

There is no month or year offset because the AY Month Sort determines the order of months in a visual. So that July is always the first month of the academic year and June is the last month of the academic year.

Melissa · February 18, 2020, 4:23pm

You’ll find the logic for that column inside the Calendar function within the attached PBIX

Let me know if you have any problems.

jmwdba · February 18, 2020, 4:37pm

I was looking at that segment in the code. I guess I just really don’t understand it or how to adjust it to even work with my dates table. There are no offset columns I could add but I dont understand the basis of the offset.

Melissa · February 18, 2020, 4:42pm

In your m code there is a reference to a query can you provide that? Then I’ll take a look for you.

jmwdba · February 18, 2020, 4:44pm

let
Query = (StartDate as date, EndDate as date, AYStartMonth as number) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate)),
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{“Column1”, type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{“Column1”, “Date”}}),
InsertYear = Table.AddColumn(RenamedColumns, “Year”, each Date.Year([Date]),type text),
InsertYearNumber = Table.AddColumn(RenamedColumns, “YearNumber”, each Date.Year([Date])),
InsertQuarter = Table.AddColumn(InsertYear, “QuarterOfYear”, each Date.QuarterOfYear([Date])),
InsertMonth = Table.AddColumn(InsertQuarter, “MonthOfYear”, each Date.Month([Date]), type text),
InsertDay = Table.AddColumn(InsertMonth, “DayOfMonth”, each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, “DateInt”, each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, “MonthName”, each Date.ToText([Date], “MMMM”), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, “MonthInCalendar”, each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & " " & Number.ToText([Year])),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, “QuarterInCalendar”, each “Q” & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year])),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, “DayInWeek”, each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, “DayOfWeekName”, each Date.ToText([Date], “dddd”), type text),
InsertWeekEnding = Table.AddColumn(InsertDayName, “WeekEnding”, each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, “Week Number”, each Date.WeekOfYear([Date])),
InsertMonthnYear = Table.AddColumn(InsertWeekNumber,“MonthnYear”, each [Year] * 10000 + [MonthOfYear] * 100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,“QuarternYear”, each [Year] * 10000 + [QuarterOfYear] * 100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{“QuarternYear”, Int64.Type},{“Week Number”, Int64.Type},{“Year”, type text},{“MonthnYear”, Int64.Type}, {“DateInt”, Int64.Type}, {“DayOfMonth”, Int64.Type}, {“MonthOfYear”, Int64.Type}, {“QuarterOfYear”, Int64.Type}, {“MonthInCalendar”, type text}, {“QuarterInCalendar”, type text}, {“DayInWeek”, Int64.Type}}),
InsertShortYear = Table.AddColumn(ChangedType1, “ShortYear”, each Text.End(Text.From([Year]), 2), type text),
AddAY = Table.AddColumn(InsertShortYear, “AY”, each “AY”&(if [MonthOfYear]>=AYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
in
AddAY
in
Query

Melissa · February 18, 2020, 4:50pm

Excellent, thanks
I’m heading out now but I ll pick this up later today.

Melissa · February 18, 2020, 8:51pm

@jmwdba, I added both TermStart and MonthOffset columns to your Date table.

and also updated the Student count 2 measure to work of the Date MonthInCalendar column.

Student count 2 = 
VAR TermStart = LOOKUPVALUE( Dates[TermStart] | Dates[MonthInCalendar] | SELECTEDVALUE(Dates[MonthInCalendar]))
VAR MonthOffset = LOOKUPVALUE( Dates[MonthOffset] | Dates[MonthInCalendar] | SELECTEDVALUE( Dates[MonthInCalendar] ))
VAR OffsetNumber = IF( TermStart >0 | MonthOffset - TermStart | MonthOffset )

VAR StudentsEnrolledTermStart =
    CALCULATETABLE ( VALUES ( 'Enrolled data 2'[Student ID] )|
        FILTER ( ALL ( Dates )|
        Dates[MonthOffset] = OffsetNumber
        )| FILTER( 'Enrolled data 2' |
        'Enrolled data 2'[Enrollment Status] IN { "Enrolled in Future Term" | "Enrolled in Term" }
        )
    )
VAR StudentsEnrolled =
    CALCULATETABLE ( VALUES ( 'Enrolled data 2'[Student ID] )|
        FILTER ( ALL ( Dates )|
        Dates[MonthOffset] = MonthOffset
        )| FILTER( 'Enrolled data 2' |
        'Enrolled data 2'[Enrollment Status] IN { "Enrolled in Future Term" | "Enrolled in Term" }
        )
    )
RETURN

    COUNTROWS ( INTERSECT( StudentsEnrolled | StudentsEnrolledTermStart ) )

Here’s the updated file.
eDNA- Include individual if not in previous 2 months v2.pbix (444.0 KB)

jmwdba · February 18, 2020, 9:15pm

We have magic!!! I just need to take some time and think about about the narrative so that I don’t confuse myself. There is something else I need to do I just cant think of it right now to articulate it well. It has to do with the Enrolled in Future Term individuals.

You Are Awesome!!!

Melissa · February 18, 2020, 9:31pm

No problem. And should new issue arise just open up a new thread.
There are always members willing to help out on the forum.