Last value in a month

[TomBond77]

Hi experts

For a product we need the last "stock" value in a month. As you can see in this sample the last value in March 2022 is zero on 22.03.2022. This value is needed in the result without day in the drill down. Any ideas?

[hic · Accepted solution]

Use Year and Month as dimensions, and the following measure:

FirstSortedValue(Value, -1*Date)

If there is more than one "Value" within a date, you may need to make a nested aggregation:

FirstSortedValue(Aggr(Sum(Value),Date), -1*Date)