Distinct values at max date

[Hoowling]

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

[sunny_talwar]

May be this

Sum(Aggr(
    If(Date = Max(TOTAL <VKRIDN> Date), Sum([Planned qty]))
, VKRIDN, Date))

[Hoowling]

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

[vamsee]

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.

[sunny_talwar]

I think it would be more like this

Sum({<Date = {"$(=Date(Max({1} Date)))"}>} [Planned qty])

[Hoowling]

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). 

[Hoowling]

Sunny - This alternative returns null 

[sunny_talwar]

Would you be able to share a sample where we can see the issue?

[Hoowling]

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

[vamsee]

Hi Hoowling,

I tweaked the expression a little, created a variable vMaxDate and used that in the expression.