cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## Need to return single date for each year for rolling average measure

Hi everyone,

I'm looking to calculate a rolling average, but first I need the date in which it will calculate over. My dataset currently contains 19 years worth of information, each identified with a "Season" (ex: 2021, 2022, etc). I need to identify the last date ("date_closed") of each season.

So far, I've tried some measures combining MAX(date_closed) & FILTER for "Season", but I haven't gotten what I want

I get my result in Excel, but I'm not too versed in DAX yet. Any help is appreciated

1 ACCEPTED SOLUTION
Super User

is this what you want?

``Column = CALCULATE(max('Table'[date]),ALLEXCEPT('Table','Table'[season]))``

Proud to be a Super User!

5 REPLIES 5
Super User

could you pls provide the sample data and the expected output?

Proud to be a Super User!

Frequent Visitor

@ryan_mayu I can't provide the sample data, but maybe I can clarify a bit more.

Each row of my data is assigned a "season" (a year) and I would eventually like to calculate a rolling average over the "seasons". However, I need to use the final data for each season, which is associated with a particular date - the MAX of "date_closed". Final data is the accumulation of all data up until the latest date in each season.

Expected result:

If the MAX date of "Season" 2020 is 4/1/2020, all rows with 2020 as the season must show 4/1/2020 as the final date (in a column)

Super User

is this what you want?

``Column = CALCULATE(max('Table'[date]),ALLEXCEPT('Table','Table'[season]))``

Proud to be a Super User!

Frequent Visitor

Yes, I believe this will work! The result is exactly what I'm aiming for. Thank you so much.

Super User

you are welcome

Proud to be a Super User!

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors