topic YTD Total of Max values in DAX Commands and Tips

YTD Total of Max values

Anonymous — Tue, 31 Jan 2023 21:12:45 GMT

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!

Re: YTD Total of Max values

FreemanZ — Tue, 31 Jan 2023 22:46:47 GMT

hi Anonymous

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

)

Re: YTD Total of Max values

Anonymous — Wed, 01 Feb 2023 09:31:04 GMT

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.

Re: YTD Total of Max values

FreemanZ — Wed, 01 Feb 2023 10:19:03 GMT

hi Anonymous

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

)

Re: YTD Total of Max values

Anonymous — Wed, 01 Feb 2023 17:08:57 GMT

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?

Re: YTD Total of Max values

FreemanZ — Thu, 02 Feb 2023 05:48:13 GMT

hi Anonymous

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.

Re: YTD Total of Max values

Anonymous — Thu, 02 Feb 2023 13:43:21 GMT

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.