Count number of batches based on the latest UD code

[Nickolay1]

Hi All,

Hopefully you can help me here. I need to build a bar chart that will have GR month as X axes and number of batches as bars. Based on the dataset below every month a snapshot is taken. And it contains a history of all the UD codes that batch received. For example if the batch 1 had GR in Nov 23 and received its UD code 0 also in Nov 23, then it received next UD code 60 in Jan 24; in this case if we use the data set below this batch will be counted 2 times in the chart. I wonder how can we ensure that this batch is counted only once based on its latest UD code. 

I tried to use a condition for maximum UD code, but it didn't work: 

({<[UD Code]={"$(=max([UD Code]))"}>}

Also Distinct operator doesn't help

Data Month - Year	Batch Number	GR Date	UD Code	Usage Decision Date
Nov-2023	1	13/11/23	-	-
Dez-2023	1	13/11/23	0	10/11/23
Jan-2024	1	13/11/23	0	10/11/23
Jan-2024	1	13/11/23	60	26/01/24
Feb-2024	1	13/11/23	0	10/11/23
Feb-2024	1	13/11/23	60	26/01/24
Mär-2024	1	13/11/23	0	10/11/23
Mär-2024	1	13/11/23	60	26/01/24

[BrunPierre · Accepted solution]

It should work like this.

=Count(DISTINCT If( [UD Code] = Aggr(Max(TOTAL <[Data Month - Year]> [UD Code]), [UD Code], [Data Month - Year]), [Batch Number]))

[Chanty4u · Accepted solution]

Try this.count(distinct {<

    [UD Code] = {"$(=max(aggr(max([UD Code]), [Batch ID])))"}

>} [Batch ID

])

[Nickolay1]

Thanks all! It worked. The key is to use aggregation formula. I also realized that it's better to use combo chart here and create separate measures for each stack.