Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am attempting to show the sum of values purely for the last day of any given month. When I try
sum({($<[Financial year] = {$(=Max([Financial year]))},Day={$(=day(MonthEnd(MakeDate(if(num(Month)< FYFirstMonth,max([Financial year]),max([Financial year]) - 1),num(Month)))))}>)} [Month end balance])
I get nothing. If I try the Day expression as a value on the axis rather than part of a set expression, it shows the correct values:
day(MonthEnd(MakeDate(if(num(Month)< FYFirstMonth,max([Financial year]),max([Financial year]) - 1),num(Month))))
. I have managed to frig the set by hardcoding:
sum({($<[Financial year] = {$(=Max([Financial year])-1)},Day={28, 29, 30, 31}>)} distinct [Month end balance])
but that is profoundly unsatisfactory, and then if there are values on more than one of the last four days of a 31 day month it will skew the result. Is there any way for the first expression to work?