Reply
MLea
Frequent Visitor
Partially syndicated - Outbound

Display one monthly amount in a matrix with fortnightly dates

Hi, 
I have a Power query table with amounts sporadically spread across month/year dates, by User ID.

MLea_2-1709012216542.png


I then have a matrix (with a page filter by User ID) with fortnightly dates listed with amounts from measures created in DAX (these amounts display correctly).

MLea_1-1709009098889.png

 

I need to get the monthly amounts in the first table to display in the matrix without duplicating. It doesn't matter which date within a month the amount sits against, only that it displays only once.
I have tried many, many ways to achieve this but cannot get it to only display once.
Any help would be very appreciated 🙂 
thank you

FYI
1. The closet I came to getting it to work was using an append table (of the 2 other sets of data i have) and the following measure (with the aggregation of the value set to Maximum):

MLea_0-1709013911696.png

 

2. I have also created a merged table that contains all sources of data (with an index) if that is more useful.

1 ACCEPTED SOLUTION
MLea
Frequent Visitor

Syndicated - Outbound

Given up trying to get the correct amounts displaying within the same table. I have just created another table that will display amounts based on slicer selections. not perfect but gives the required data.

View solution in original post

10 REPLIES 10
MLea
Frequent Visitor

Syndicated - Outbound

Given up trying to get the correct amounts displaying within the same table. I have just created another table that will display amounts based on slicer selections. not perfect but gives the required data.

danextian
Super User
Super User

Syndicated - Outbound

Hi @MLea ,

Please post a workable sample data that is not an image.

 

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Syndicated - Outbound

Hi @danextian ,

sorry this is my first time posting a question. Do you mean replace the screen shots with 'tables' of data?
I can't attach the working file due to security/privacy issues.

Syndicated - Outbound

You can just reply to my other post. Paste sample data from Excel to it or post a link to an Excel file in the cloud.





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Syndicated - Outbound

Hi @danextian 
i have extracted data here: Testing v1.pbix

Syndicated - Outbound

Hi,

See the attached PBI file.  Is this better?

Ashish_Mathur_0-1709262984558.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Syndicated - Outbound

Thank you for looking into my problem @Ashish_Mathur 
The AA amount looks correct but the dates in the first column (under quarters) need to be the 'Amount_Post Date' as these dates are when the amounts are posted into the GL and the second column needs to be the 'Amount' column (being the amounts posted to the GL).
I need to match the assistant amounts by the month, while also accepting the page filter.

Syndicated - Outbound

Hi,

Just ensure that your Calendar Table starts from 1/1/2023.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Syndicated - Outbound

Hi, when the power bi file is connected to the actual data (not the sample I submitted) it will be for data over numerous years so the calendar start date can not be changed to 01/01/2023.

Syndicated - Outbound

May be someone else can pitch in.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

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

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)