Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community,
In a chart, I want to calculate the "planned qty" per VKRIDN (order number) at the max date.
An example of the raw data is found in below table.
Basically I want the chart to show 576 units on 2020-07-01.
Currently I'm using this formula: Sum(aggr(sum(Distinct[Planned qty]),VKRIDN)) - which puts the quantity at 2020-06-30.
Any suggestions on how to include a max(Date) function in the set analysis above?
Regards,
Hoowling
May be this
Sum(Aggr(
If(Date = Max(TOTAL <VKRIDN> Date), Sum([Planned qty]))
, VKRIDN, Date))
That worked when no date selections are made, but when I select a particular month the max date changes.
So if I select June, the planned qty of 576 appears in June, whereas if I select July the qty appears in July.
In this case I want the planned qty to be frozen to July (i.e. max date).
Regards,
Hoowling
Try
Sum({<Date={"$(=Date(Max(Date)))"}>}[Planned qty])
Note: If you have Date as a dimension in the chart, for all the records it would show the latest value only.
I think it would be more like this
Sum({<Date = {"$(=Date(Max({1} Date)))"}>} [Planned qty])
Vamsee - This had no effect, the planned qty end up in the month that I select instead of just appearing in July (thus, should be zero if June is selected).
Sunny - This alternative returns null
Would you be able to share a sample where we can see the issue?
Attached is a very simple example to highlight the problem.
Again - I want the table to always show the planned qty @ max date. So if I select 20200630 planned qty should be 0, and when selecting 20200701, planned qty should be 576
Regards,
Hoowling
Hi Hoowling,
I tweaked the expression a little, created a variable vMaxDate and used that in the expression.