Values that pertain to the most recent or last month of the quarter

[Anonymous]

Hi. I need help creating a column(Qtr_Inspections) that has values that pertain to the most recent or last month of the quarter. Basically, I just want the last value of every Quarter into this new column. 

Here's what I have:

Date	Quarter	Inspections
1/1/2020	Qtr 1	0
2/1/2020	Qtr 1	2
3/1/2020	Qtr 1	4
4/1/2020	Qtr 2	10
5/1/2020	Qtr 2	3
6/1/2020	Qtr 2	7
7/1/2020	Qtr 3	11
8/1/2020	Qtr 3	2

Here's what I need:

Date	Quarter	Inspections	Qtr_Inspections
1/1/2020	Qtr 1	0	4
2/1/2020	Qtr 1	2	4
3/1/2020	Qtr 1	4	4
4/1/2020	Qtr 2	10	7
5/1/2020	Qtr 2	3	7
6/1/2020	Qtr 2	7	7
7/1/2020	Qtr 3	11	2
8/1/2020	Qtr 3	2	2

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create a measure or calculated column as below:

Measure =
VAR _maxdate =
    CALCULATE (
        MAX ( 'Inspections'[Date] ),
        ALLEXCEPT ( 'Inspections', 'Inspections'[Quarter] )
    )
RETURN
    CALCULATE (
        MAX ( 'Inspections'[Inspections] ),
        FILTER (
            ALLSELECTED ( 'Inspections' ),
            'Inspections'[Quarter] = MAX ( 'Inspections'[Quarter] )
                && 'Inspections'[Date] = _maxdate
        )
    )

Calculated column:

Qtr_Inspections = 
VAR _maxdate =
    CALCULATE (
        MAX ( 'Inspections'[Date] ),
        ALLEXCEPT ( 'Inspections', 'Inspections'[Quarter] )
    )
RETURN
    CALCULATE (
        MAX ( 'Inspections'[Inspections] ),
        FILTER (
            'Inspections',
            'Inspections'[Quarter] = EARLIER ( 'Inspections'[Quarter] )
                && 'Inspections'[Date] = _maxdate
        )
    )

Best Regards

Rena

[amitchandak]

@Anonymous , Try like

calculate( Sum(Table[Inspections]), filter( Table,Table[Date] = max(Table[Date])), allexcept(Table, Table[Quarter]))

or

calculate( Sum(Table[Inspections]), filter( Table,format(Table[Date],"YYYYMM") = format(max( Table[Date]),"YYYYMM")), allexcept(Table, Table[Quarter]))

[Anonymous]

Thanks @amitchandak. Unfortunately, this doesn't get me what I want. Any other ideas?

[amitchandak]

@Anonymous , Please find the attached file after signature