The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
Date | Pension 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:
Date | Pension 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
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
User | Count |
---|---|
10 | |
9 | |
6 | |
6 | |
5 |
User | Count |
---|---|
21 | |
14 | |
14 | |
9 | |
7 |