Overlapping Date Range Puzzle

[Anonymous]

Hi,

I wonder is anyone could help with this Power BI / DAX puzzle that has been causing my brain to do back flips over the past couple of days?

I'm trying to determine the number (COUNT) of students that apply for a course during a specific cycle, defined by start and end dates in the Application Cycle table below. The application cycles each cover two years and are tied to the Academic year in which a student course is due to start. (See table below). Ordinarily I’d simply add the application cycle to my Date table for every date, but the cycles overlap (any given application can count in two cycles) so thought it best to have a separate dimension table.

I’ve set the model up in the way shown below with the Application cycle separate from my Date table but not sure if this is the right approach (I guess it could be snow flaked off the date table). Ultimately, I’m looking to generate a simple(!) line graph visual that shows the July -> (June + 2 Years) months / weeks of the cycles on the axis and the different application cycles as the lines so they can be compared.

Does anyone have any ideas or comments how to approach this (and save my aching brain!)?

Thanks very much and best regards

John

[Anonymous]

Thanks. I've already got the MonthYear part sorted by Application cycle (see below) but I was really looking to layer these on top of one another so that the x-axis covers two years (July to July + 2 Years) rather than being one continuous line, if you see what I mean.

Best Regards

John

[johnt75 · Accepted solution]

I think I see. You could maybe add another couple of columns to your date table, one with the month name and one with a sort order that returns 1 for July, 2 for August through to 12 for June. sort the new month name column by that sort order column and use it on your visual

[johnt75]

create a "Year Month" column on your Date table, and also a "Year Month sort column". The "Year month" column can be any format you like, mmm YYYY or YYYY mmm or whatever. The sort column needs to be numeric, so YYYYMM. Set the Year Month column to be sorted by the sort column and add that to your axis instead of just month.

[Anonymous]

Thanks very much, that seems to have done the trick. 🙂

Struggling a bit to get the months on the x-axis to  show for two years . I've tried creating a "Phase" column in the  Application Cycle table that concatenates the phase number (1 or 2) month number (for sorting) and the Month name. Unfortunately getting some pretty cranky results! Ideally I'd like the dates on the x-axis to run from 1st July to 1st July + 2 years to match the application cycle. 

Any thoughts?

Thanks again and best regards

John

[johnt75]

Maybe change your Application Cycle table to have an entry for each date within the cycle for every cycle, so you'd have 2 years worth of dates for each cycle. Remove the relationship with the Application Records table and create a many-to-many relationship with the Date table such that any filters on the Application Cycle table flow to the Date table and then on to the Application Records table. I don't think that filters would need to flow in the other direction, so the many-to-many relationship wouldn't need to be bidirectional.