YTD Total of Max values

[ChrisH]

I've reached my wits end with this one, been at this for weeks and i'm no closer to an answer

Problem - I have a dataset of historical data of student counts, The dataset has a years worth of dates, but for each date there are 5 rows showing the equivalent days student counts for historical years, 2021,2018,2019 etc. I need to find the YTD Total of MAX student counts possible for that day in the previous 5 years. So the max value might be from 2019 on one date and 2018 on another. 

Once i've identified a MAX for each day in the year i need to have a YTD total for these values. I also need to exclude the current years data from the dataset.

Equiv Day	AcadYear	Student Count	Max YTD
01 Jan	2019	3	5
01 Jan	2018	4	5
01 Jan	2017	5 (Max Value)	5
02 Jan	2019	1	8
02 Jan	2018	3 (Max Value)	8
02 Jan	2017	2	8
02 Jan	2019	4 (Max Value)	12
02 Jan	2018	2	12
02 Jan	2017	1	12

I've been using something like
MAX =

var equivDay = Dim[Equiv Day]
var latestyear = YEAR(MAX(Dim[AcadYear]))

return

CALCULATE(MAX(Dim[Student Count]),
Dim[Equiv Day] = equivday,
Dim[AcadYear] <>latestyear)

Then using a DATEYTD(SUM(MAX),Dim[EquivDay]) to calculate the YTD total. I've tried using a SUMMARIZE function to do this but none of this is working and i'm ready to scream. Any suggestions welcome!

[FreemanZ]

hi @ChrisH 

try to plot a table visual with the Equiv Day column and a measure like this:

Max YTD =

VAR _table1 =

ADDCOLUMNS(

    SUMMARIZE(

        VALUES(TableName[Equiv Day],

        "DayMax",

         CALCULATE(MAX(TableName[Student Count]))

)

VAR _table2 =

TREATAS(

    _table1,

    TableName[Equiv Day],

    TableName[Student Count]

)

RETURN

CALCULATE(

    SUM(TableName[Student Count]),

    TableName[Equiv Day]<=MAX(TableName[Equiv Day]),

    _table2

)

[ChrisH]

Hi @FreemanZ 

Looks like the first variable is missing an arguement in ADDCOLUMNS. "Too few arguments were passed to the ADDCOLUMNS function. The minimum argument count for the function is 3."

I've been trying to figure this one out myself but any advice on whats missing would be greatly appreciated.

[FreemanZ]

hi @ChrisH 

there is a typo, please try:

Max YTD =

VAR _table1 =

ADDCOLUMNS(

        VALUES(TableName[Equiv Day],

        "DayMax",

         CALCULATE(MAX(TableName[Student Count]))

)

VAR _table2 =

TREATAS(

    _table1,

    TableName[Equiv Day],

    TableName[Student Count]

)

RETURN

CALCULATE(

    SUM(TableName[Student Count]),

    TableName[Equiv Day]<=MAX(TableName[Equiv Day]),

    _table2

)

[ChrisH]

Thanks @FreemanZ 

Think i'm getting somewhere with this. Measure is returning the correct MAX values for each Equiv Day. Only thing i'm stuck on now is the Rolling total. Wondering if this is something to do with not having a date table? Currently returning the Max for each date, presumably the filter context isn't working for the <=Max Date?

[FreemanZ · Accepted solution]

hi @ChrisH 

try like:

Max YTD =

VAR _table1 =

ADDCOLUMNS(

        ALL(TableName[Equiv Day],

        "DayMax",

         CALCULATE(MAX(TableName[Student Count]))

)

VAR _table2 =

TREATAS(

    _table1,

    TableName[Equiv Day],

    TableName[Student Count]

)

RETURN

CALCULATE(

    SUM(TableName[Student Count]),

    TableName[Equiv Day]<=MAX(TableName[Equiv Day]),

    _table2

)

if the issue persist, could you please provide an expanded dataset and let me verify the code. 

[ChrisH]

Hi @FreemanZ 

I eventually figured out the issue, for some reason the 
TableName[Equiv Day]<=MAX(TableName[Equiv Day]) filter was working incorrectly. I removed the link to the date table which fixed it. I'm not quite sure why the existence of the relationship was causing it but the DAX works perfectly now! Thanks for all your help.