Filtering by Date Table Wonkiness

[crispybits77]

Hi all

Having some trouble getting a measure to work how I want. The end goal is for an attendance to date for the year graph for a group of 3 schools similar to this:

The 3 part lines are for this year's data, the 3 paler lines are for last year's data. The measure they have used for this is as follows:

AttendYTD%Day = 
CALCULATE (
   Attendance_Calc[%Att2],
FILTER (ALL(DateTable[Day Order]),
        DateTable[Day Order] <= 
MAX(DateTable[Day Order])
    )
)

%Att2 measure references is just number of days present divided by number of days possible to be in school, with BLANK() as the fallback value. Day order is just the numbers 1-365(366) with 1 being the first day of the academic year. The lines are created by having a column on the attendance chart for the school code and then academic year, so for example ABC2022, DEF2023, etc and the visual is filtered to only the desired school/year combinations in the "Legend" data setting.

I've done exactly the same with this other set of data, though the names are slightly different but the calculations and relationships are all the same:

AttendYTD%Day = 
CALCULATE (
   'Attendance Calcs'[%Att],
FILTER (ALL('Primary Date Table'[DayOrder]),
   'Primary Date Table'[DayOrder] <=
MAX('Primary Date Table'[DayOrder])
    )
)

Again the %Att measure referenced here is just number of present days divided by number of possible days with BLANK() as the fallback value. However when I put this into a visual I get this:

As you can see my strongly coloured lines for this year don't stop at today, they just flatline at the same number and carry on across the graph.

I've checked all the "show items with no data" type formatting settings and they all appear to be the same. I've checked and both date tables go off years into the future, and both attendance tables only contain actual data for historic days, there's nothing in there as a placeholder for future days. The relationship between the tables in the model appears to be identical.

I tried creating a column with an absolute days number (so year 1 is 1-365, year 2 is 371-735, etc using this code:

AbsDayOrder = 'Primary Date Table'[DayOrder] + (370 * ('Primary Date Table'[Year]-2016))

I thought maybe the fact the 1-365 were repeating for every year was doing something odd, but if I them use that instead of DayOrder in my measure the line stops correctly but the graph goes completely mental:

This looks to me like just changing that has somehow changed the measure from showing the complete YTD number to just showing the % on a specific day, which makes no sense given the <= in the measure formula.

Can anyone give me some pointers to try and work out what's happening here please? I just want this year's lines to end on today's date and every variation I try doesn't want to work, despite me lifting the calculations directly from a model with a nearly identical structure that does have it working the way I want...

Thanks

[CoreyP · Accepted solution]

What if you try adding a clause to only do the calculation if the date is on or before today? You might need to adjust this code, but something like this?

AttendYTD%Day = 
VAR _selecteddate = MAX( 'Primary Date Table'[Date] )
VAR _measure =
CALCULATE (
   'Attendance Calcs'[%Att],
FILTER (ALL('Primary Date Table'[DayOrder]),
   'Primary Date Table'[DayOrder] <=
MAX('Primary Date Table'[DayOrder])
    )
)
VAR _logic = IF( _selecteddate <= TODAY() , _measure , BLANK() )

RETURN
_logic

[crispybits77]

That worked thanks - got blinkered on getting the formula I had working instead of just seeing the obvious solution...

(aslo sorry for the delay replying - been off for christmas and only just getting back to work now)