Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
EDIT: I somehow found a solution, but I have no idea why it works - can anyone advise?
sum({< [Report Date] = {"=[Report Date]=Aggr(nodistinct max([Report Date]),[Report Date.autoCalendar.YearMonth])"} >} [BO Value])
the old calculation was
sum({< [Report Date] = {"=[Report Date]=Aggr(nodistinct max([Report Date]),[Report Date.autoCalendar.YearMonth])"} >} [BO Value])
As showed in the screenshot below, I am trying to get the Sum of [BO value] for the last Report Date of each month.
For some reason, for the last month (July), I don't get the value of the last Report Date but the one just before.
aggr( max([Report Date]), [Report Date.autoCalendar.YearMonth]) shows "10/07/2023" but on the wrong line (03/07/2023). The same issue is reflected when using this in the set analysis (the result is sum(BO Value) for the Report Date 03/07/2023!
What I would like to get are the highlighted values here below (which I can see using nodistinct... problem is that when using nodistinct, I don't get any "filtering" and all the values for all report dates are summed up)
Using an if statement as the one below does not seem to be a solution when combined with other dimensions (Year-Month Projection in the example)