Show the last value of each quarter

[SG-01]

Hi there,

I have the following data:

Quantity	Date
20	31-1-2023
10	10-2-2023
10	20-2-2023
30	20-3-2023

I want to show in my visual for Q1 the quantity, but only the latest one, so the quantity of 30 for 20-3-2023. If I now select Q1 in my slicer, it shows the total quantity for Q1. What kind of measure can I make that it will always shows the latest value of each Quarter?

Thanks!

[v-yangliu-msft · Accepted solution]

Hi  @SG-01 ,

I created some data:

Here are the steps you can follow：

1. Create measure.

average quantity for each quarter =
var _select=SELECTEDVALUE('Table'[Quarter])
return
AVERAGEX(FILTER(ALL('Table'),'Table'[Quarter]=_select),[Quantity])

last value of each quarter =
var _select=SELECTEDVALUE('Table'[Quarter])
var _maxdate=
MAXX(FILTER(ALL('Table'),'Table'[Quarter]=_select),[Date])
return
SUMX(FILTER(ALL('Table'),'Table'[Date]=_maxdate),[Quantity])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[SG-01]

Thanks so much!

And what if my table has an extra column with months where I have another slicer with month numbers. So if you click on more than one month number, it also calculates the average, but if the input is just one month number it shows the value for that month. I need that in the same measure as for the quarters. So based on the input of the slicer the value will be showed.

And how can I change the measure for the average quantity that it will also calculate the average when multiple quarters are selected. That does not seem to work right now.

[JonasBI]

Measure = CLOSINGBALANCEQUARTER(SUM(Table[Quantity]), Table[Date]

[SG-01]

@JonasBI Does not seem to work, I get no values back.

Maybe better for me that I want the average of each quarter of a measure. So the measure calculates the average quantity for each quarter. How can I generate that measure?