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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MLea
Frequent Visitor

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

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

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

Hi @MLea ,

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

 

 










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


Proud to be a Super User!









"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.

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.

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.










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


Proud to be a Super User!









"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.

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

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/

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.

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/

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.

May be someone else can pitch in.


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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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