how to get a sum according to maximum dates

[Lejouquard]

Hi Guys,

I have the folowing data in a table (ROWIND)  :

The Date is linked to a calendar table which goes from 01/01/2018 to 31/12/2021.

I would like create a measure to get this visual  :

It means for each period (Here it's years but it could be quarters or months)

=> to have the sum of the maxdate of the period of the visual (dimdate). 

In my example : Max date 2018 = 31/12/2018 | Max date 2019 is = 31/12/2019 etc. and get the related sum value of these max date of the table ROWIND.

And this is not all because as you see in my example for 2021, I have no date value in ROWIND in 2021. So in this case I would like to have the sum of the MaxDate of ROWIND (here maxdate = 31/05/20 and sum = 990)

this is the value results I would like : 

Hope my explanation are clear ?

Thank you for your help ! 🙂

[v-yangliu-msft]

Hi  @Lejouquard  ,

Here are the steps you can follow：

1. Create calculated table.

Date = CALENDAR(DATE(2018,1,1),DATE(2021,12,31))

2. Create measure.

Measure =
var _year=YEAR(MAX('Date'[Date]))
return
SWITCH(
    _year,
    2018,0,
    2019,SUMX(FILTER(ALL('ROWIND'),[date]=MAXX(FILTER(ALL(ROWIND),YEAR([date])=2019),[date])),[Totexprr]),
    2020,SUMX(FILTER(ALL('ROWIND'),[date]=MAXX(FILTER(ALL(ROWIND),YEAR([date])=2020),[date])),[Totexprr]),
    2021,SUMX(FILTER(ALL('ROWIND'),[date]=MAXX(ALL(ROWIND),[date])),[Totexprr]),
    BLANK())

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

[PaulDBrown · Accepted solution]

@Lejouquard 

Try:

Assumin you have a measure for the sum of ROWIND[TOTEXPRR]

Sum on max date =
VAR _LastDate =
    CALCULATE (
        MAX ( Calendar[Date] ),
        FILTER ( Calendar, NOT ( ISBLANK ( [Sum TotExprr] ) ) )
    )
RETURN
    CALCULATE ( [Sum TotExprr], FILTER ( Calendar, Calendar[Date] = _LastDate ) )