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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
GurraGuru
Regular Visitor

Capture the number of contracts and the highest number of invoices issued as of a certain period

Hi,

I am trying to achieve something that seems pretty straight forward, but I keep failing so I thought I'd better try my luck in this forum. 🙂 I have a list with contract IDs and invoices and I want to capture, for a certain period,  the number of contracts and how many times an invoice has been issued for these. So in the below example, if I choose April and May I would like the results I show to the right. If I select only the latest invoice period I get the result I want, but when I select a previous period it either calculates with the maximum number of invoices issued in total (so for MNO it gives me 9 instead of 8. Or, depending on what faulty codes I use, if I look at a certain contract, it seems to count the invoices instead of the contracts (so if I filter on LMN it would give me one match for 1 invoice issued and another match for 2 invoices issued - there should only be one match for 2 invoices for the period April-May). 

I don't know if I have expressed this clear enough, but I hope thyou will be able to understand my issue.

Thanks
Gunnar K 

GurraGuru_0-1700564346246.png

 

5 REPLIES 5
lbendlin
Super User
Super User

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Hi, thanks for your input. Meanwhile I've come a bit further. Now I get the correct result in the table visual containing Contraxt ID and Max invoice, but when I try to group the values in the visual below it doesn't reflect that. If I only select one month it is OK, but when I select two or more the max invoice is counted for each month, not only the max for the selected period. 

Sample data is here:
https://1drv.ms/u/s!AreREkMzNQgbvGWM_m5LaFbC_E3P?e=kZA3Bu


GurraGuru_0-1700737374122.png

 

lbendlin_0-1700860836561.png

see attached

 

Thanks! But I still need the column with the total number of invoices per contract. If Feb-Mar is selected, there may be contracts that have been invoiced in previous months. So for a specific contract the February invoice may be the 6th in total, and the March invoice the 7th. Then the resultat for this contract would be 7, not 2 even though only two invoices have been issued in the selected period. 

Change the filter in line 3 to not test against the minimum selected date (remove the Minsta check).

 

lbendlin_0-1701047468960.png

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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