cancel
Showing results for
Did you mean:
Frequent Visitor

## Find the most recent value during a quarter

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,

1 ACCEPTED SOLUTION
Community Support

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!

5 REPLIES 5
Community Support

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!

Frequent Visitor

Exactly what I needed, thank you!

Super User

Try

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

@Booyah , use a date table, try measure like

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

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!
Frequent Visitor

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?

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors