DAX YTD measure nearly works in 2 different ways but both are wrong

[crispybits77]

Hi all

I have a measure I can write two different ways, but both come out slightly wrong. For context this is school attendance data and we want to show the cumulative attendance of students for the year split out by week. The first try looked like this:

% AttendYTD Wk = 
CALCULATE (
   'Attend Calcs'[% Att],
FILTER (ALL(DateTable[Week]),
        DateTable[Week] <= 
MAX(DateTable[Week])
    ))

This seemed to give the correct calculation number, but when we put it into a visual because of the ALL it didn't only show the weeks we've completed this year, it showed future weeks with a % figure too.

So instead I tried this:

% AttendYTD Wk = 
CALCULATE (
   'Attend Calcs'[% Att],
FILTER (DateTable,
        DateTable[Week] <= 
MAX(DateTable[Week])
     && DateTable[Acad Year]=2025))

This limited us to only the weeks we've completed this year, but it's giving the % number only for that specific week and not the cumulatve YTD.

How do I do both, limit the weeks to only see this year's data, and add the additional filter for academic year so that it gets around needing to use the "ALL"?

This is a sample of the DateTable so you can see the format:

Date	Week	Half Term	Year	Order	W/b	W/b Order	Acad Year
05/09/2022	1	1	2023	1	5/9	1	2023
06/09/2022	1	1	2023	2	5/9	1	2023
07/09/2022	1	1	2023	3	5/9	1	2023
08/09/2022	1	1	2023	4	5/9	1	2023
09/09/2022	1	1	2023	5	5/9	1	2023

[crispybits77]

@danextian got it thanks - one small modification was needed still to my formula to just look at this academic year (sorry I wasn't clear on that bit):

% AttendYTD Wk = 
VAR MaxDate = LASTNONBLANK(DateTable[Date],'Attend Calcs'[% Att])
RETURN
CALCULATE (
   'Attend Calcs'[% Att],
FILTER(
    ALL(DateTable),DateTable[Week] <= MAX(DateTable[Week]) && DateTable[Date] <= MaxDate && DateTable[Acad Year]=2025))

All works perfectly now thanks.

[crispybits77]

@danextian thanks for the reply, unfortunately it hasn't worked. I don't know if that's because I'm trying to create this as a standalone measure and not a column in the date table?

My new code:

% AttendYTD Wk = 
VAR MaxWeek = LOOKUPVALUE(DateTable[Week],DateTable[Date],UTCTODAY())
RETURN
CALCULATE (
   'Attend Calcs'[% Att],
FILTER(
    ALL(DateTable),DateTable[Week] <= MAX(DateTable[Week])
    && DateTable[Week] <= MaxWeek))

The LOOKUPVALUE variable set formula piece I've put into a separate measure and it's returning 11 which is correct, but if I create a matrix of weeks vs attendance the table it produces still goes all the way up to week 43

I need the visual to stop at the current week - starting to think I'm approaching this from entirely the wrong direction but not sure what else to do...

[danextian · Accepted solution]

Hi @crispybits77 

The formula I gave was for a measure and you're supposed to calculate the latest nonblank week number in your fact table or whatever the latest date is. If your fact table doesnt have a column for week number then you need to change the formula to calculate the latest date instead.

cumulative with stop =
VAR LatestDateWithValue =
    LASTNONBLANK ( Data[Date], SUM ( Data[Amount] ) )
RETURN
    CALCULATE (
        SUM ( Data[Amount] ),
        FILTER (
            ALL ( Dates ),
            Dates[Week] <= MAX ( Dates[Week] )
                && Dates[Date] <= LatestDateWithValue 
        )
    )

 or the latest week number based on today's date.

cumulative with stop =
VAR LatestWeekAsOfToday =
    CALCULATE (
        MAX ( Dates[Week] ),
        FILTER ( ALL ( Dates ), Dates[Date] <= TODAY () )
    )
RETURN
    CALCULATE (
        SUM ( Data[Amount] ),
        FILTER (
            ALL ( Dates ),
            Dates[Week] <= MAX ( Dates[Week] )
                && Dates[Date] <= LatestWeekAsOfToday
        )
    )

Please note that I used two tables in the formula: Dates for the dates and Data for the fact.

[anmolmalviya05]

Hi @crispybits77 , To achieve both goals—limiting the weeks to the current academic year and ensuring the measure calculates cumulative attendance year-to-date—you can refine your DAX measure to include appropriate filters while retaining the cumulative logic. Here's the revised measure:
% AttendYTD Wk =
CALCULATE(
'Attend Calcs'[% Att],
FILTER(
ALL(DateTable),
DateTable[Week] <= MAX(DateTable[Week])
&& DateTable[Acad Year] = 2025
)
)


If you want to avoid ALL(DateTable) for simplicity and your model allows it, use a more focused filter:
% AttendYTD Wk =
CALCULATE(
'Attend Calcs'[% Att],
FILTER(
DateTable,
DateTable[Week] <= MAX(DateTable[Week])
&& DateTable[Acad Year] = 2025
)
)

[danextian]

Hi @crispybits77 

That behaviour is expected if your table has dates that  are past the dates in your fact table. This portion of your formula will be applied to the current row week number regardless of whether it exists in the fact table.

MAX(DateTable[Week])

You can modify your cumulative calculation by adding a filter that includes only up to the latest date in your fact table. Please see the screenshot below:

cumulative with stop =
VAR LatestWeekWithValue =
    LASTNONBLANK ( Data[Week], SUM ( Data[Amount] ) )
RETURN
    CALCULATE (
        SUM ( Data[Amount] ),
        FILTER (
            ALL ( Dates ),
            Dates[Week] <= MAX ( Dates[Week] )
                && Dates[Week] <= LatestWeekWithValue
        )
    )