Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Martin69
Regular Visitor

How do you create a non-aggregated As-At position in an Excel Power Pivot table?

Hi

I want to create an As-At non-aggregated position in an Excel DAX pivot table.

I'm on a learning curve with DAX, so have set myself various challenges, one of which was to create a composite graphic for my pension contributions vs pension value.  This is in Excel not PBI, which appears not to have the SelectedValue function - if that even works here.

The contributions side is a fairly straightforward measure, but I am really struggling with displaying the As-At value on any given date. i.e. the column in the raw table is an as-at position with every cell populated. What I don't want to do is sum the column. (Actually I do, as it would make me a very wealthy man! 😉 )

So a two column table might look like this:

 

DatePension Value
31/01/2022   1,000
31/02/2022   1,200
15/03/2022   1,500
31/03/2022   3,000
30/04/2022   2,800
31/05/2022   2,600

 

Whilst other columns in my pivot are aggregates, I haven't been able to get it to display the As-At values by date in the same table, as per the example.

So what it's actually showing is:

 

DatePension Value
31/01/2022   1,000
31/02/2022   2,200
15/03/2022   3,800
31/03/2022   6,800
30/04/2022   9,600
31/05/2022   12,200

(As I said, this incorrect version makes me a wealthy man...!)


How would you structure the correct DAX statement to achieve this. I'd be grateful for some help!

Thanks
Martin

3 REPLIES 3
v-xiaotang
Community Support
Community Support

Hi @Martin69 

Thanks for reaching out to us.

>> one of which was to create a composite graphic for my pension contributions vs pension value

could you share some sample data of the 2 tables? thanks.

 

 

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Bump.

Thanks for the reply.  I already have above.  The additional column that I didn't share, just has ad hoc payments.  The only thing I didn't share was my basic calendar table.

Cheers
Martin

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.