Find the most recent value during a quarter

[Booyah]

I've played with this and researched online prior to posting. Other posts on the forum do not work in my situation. I need to display the sum of the most recent values in a quarter.

I'm not sure how to exactly explain this so here is some sample data:

MyDate                MyProduct            Amount

01/01/2022           Banana                   20

01/01/2022           Banana                   20

02/02/2022           Banana                   20

03/03/2022           Banana                   10

This is a "point in time" calculation so the values for Banana will not sum. Now, if there was a Pineapple product listed, then Banana and Pineapple would sum.

My desired results: I should expect to see a count of 1 for Banana with a value of 10, because in March the value changed from 20 to 10.

If the value was 20 all the way through the quarter, then I would see 20. I was able to accomplish this ^ using calculate and sumx distinct, but when the Amount changes, instead of taking the most recent value in that quarter, it will sum the two different values, resulting in a value for Banana of 30, not 10,

[v-cazheng-msft · Accepted solution]

Hi @Booyah ，

You may try this solution.

1 Use Year column and Quarter column from Calendar table to create Slicers

2 Create these two Measures

MostRecentValueInQuarter =
VAR SelectedYear =
    SELECTEDVALUE ( 'Calendar'[Year] )
VAR SelectedQuarter =
    SELECTEDVALUE ( 'Calendar'[Quarter] )
VAR LatestDate_ =
    CALCULATE (
        LASTDATE ( 'Table'[MyDateAmount] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[MyProduct] = MAX ( 'Table'[MyProduct] )
                && YEAR ( 'Table'[MyDateAmount] ) = SelectedYear
                && QUARTER ( 'Table'[MyDateAmount] ) = SelectedQuarter
        )
    )
VAR Value_ =
    CALCULATE (
        MAX ( 'Table'[Amount] ),
        FILTER ( 'Table', 'Table'[MyDateAmount] = LatestDate_ )
    )
RETURN
    Value_

Total_ = SUMX(VALUES('Table'[MyProduct]),[MostRecentValueInQuarter])

Then, the result should look like this.

Also, attached the pbix file as reference.

Best Regards,

Community Support Team _ Caiyun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!

[Booyah]

Exactly what I needed, thank you!

[johnt75]

Try

My Measure = SUMX( VALUES('Table'[Product]), 
SELECTCOLUMNS( TOPN(1, 'Table', 'Table'[Date], DESC), "@value", 'Table'[Amount])
)

[amitchandak]

@Booyah , use a date table, try measure like

calculate(sumx(summarize(Table,Table[My Product], [Amount]),[Amount]), allexcept('Date', 'Date'[Qtr Year]))

[Booyah]

Still not working like I need it to. Maybe it's something with my date table. Will you elaborate on the [Qtr Year] field I should have in the date table?