Charting Next Three Months

[WooPig]

I'm pretty inexperienced with DAX, and I've got a question about how to accomplish something.

I have data on assignments, which includes completed assignment and incomplete assignments in separate tables. Both have due date fields. The completed table also as a completed date field.

These due dates cover a wide range, from months prior (overdue assignments) to as much as a year ahead. So I don't want to chart 15 months' worth of totals. But I also don't want to do any manual maintenance every time I update the data.

We're doing a fairly simple monthly dashboard in Power View, and I want a chart that shows how many records in the incomplete table have due dates in the upcoming three months. I'm defining "next three" as the three months after whatever month the most recent completed assignment record is from. So if I've just downloaded the March data, I want to see Apr-May on my next three chart.

I found a way to make it work, but I have a feeling I did it in far from the most efficient way.

Here are my steps.

I use a calculated column to define the next three months with the values 1, 2, and 3.

Months Out=SWITCH(TRUE(),
MONTH([Unfinished Due])=MONTH(EOMONTH(LASTDATE(Completed[Completion Date]),0)+1),1,
MONTH([Unfinished Due])=MONTH(EOMONTH(LASTDATE(Completed[Completion Date]),1)+1),2,
MONTH([Unfinished Due])=MONTH(EOMONTH(LASTDATE(Completed[Completion Date]),2)+1),3,
0)

And then I do another switch to get month names.
Month=SWITCH(TRUE(),[Months Out]=0,BLANK(),FORMAT([Unfinished Due],"MMMM"))

And then I can just pop that Month column on a chart axis, tell it to ignore the blanks, and I've got my next three months.


So, is there a much simpler or more efficient way of doing this? My way feels clunky, and given my level of DAX knowledge, I just assume there's a much better way.

Thanks!

 

[scottsen]

Do you have a calendar table?

I suspect I would have a single calc column on my calendar table with a TRUE if it was 0-3 months after the last completion date, then use that column to filter my reports.