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
hgrejsen
Frequent Visitor

How to calculate First Time Through for each month - issue with the count.

Hi everyone,

 

I hope someone can help me.

 

My main goal is to get a graph showing the First Time Through in percentage.

As a table it would look like this

MinInvoiceMonth

FTT %

dec

50

nov

50

okt

100

 

And as graph:

hgrejsen_1-1686035219339.png

 

 

Where FTT is calculated as number of sales documents with Q issues / total documents.

 

I have different tables witch is used in order to identify salesorders which has a quality issue.

 

My issue is that when I do this, I can only get the overall total number of documents, it does not seem like it is only counting those that belongs to the month. This gives a wrong percentage.

 

Is there a good solution to this?

 

 

Background numbers

 

In table “Orders Invoiced” I have “Sales Document”, “Test2”, “Invoice Date”, “Sales Order Count with Q Issue by Department”.

 

 

The “Sales Document” numbers is not unique in this table

 

“Sales Order Count with Q Issue by Department” =

CALCULATE(

        DISTINCTCOUNTNOBLANK('Orders Invoiced'[Sales Document]),

                'Orders Invoiced'[Test2] = "Q issues",

                'Orders Invoiced'[Test2] <> "No issues"

)

 

Sales Document

Test2

Invoice Date

Sales Order Count with Q Issue by Department

330000233

Q issues

2. december 2023

1

330000233

Q issues

2. december 2023

1

330000233

Q issues

4. december 2023

1

330000234

No issues

2. december 2023

 

330000234

No issues

2. december 2023

 

330000234

No issues

10. december 2023

 

330000235

Q issues

2. November 2023

1

330000235

Q issues

2. November 2023

1

330000235

Q issues

6. November 2023

1

330000236

No issues

2. November 2023

 

330000236

No issues

5. November 2023

 

330000236

No issues

2. November 2023

 

330000237

Q issues

2. Oktober 2023

1

330000237

Q issues

2. Oktober 2023

1

330000237

Q issues

5. Oktober 2023

1

330000238

Q issues

2. Oktober 2023

1

330000238

Q issues

2. Oktober 2023

1

330000238

Q issues

10. Oktober 2023

1

 

 

In order to be able to make sure that I only have one Invoice month I have a separate table called ”MinInvoiceDateByOrder”

 

MinInvoiceDateByOrder =

SUMMARIZECOLUMNS(

    'Orders Invoiced'[Sales Document],

    "MinShipmentDate", MIN('Orders Invoiced'[Invoice Date])

)

 

MinShipmentDate

MinInvoiceMonth

Sales Document

2. december 2023

dec

330000233

2. december 2023

dec

330000234

2. November 2023

nov

330000235

2. November 2023

nov

330000236

2. Oktober 2023

okt

330000237

2. Oktober 2023

okt

330000238

 

The “Orders Invoiced” and “MinInvoiceDateByOrder“ is linked via the “Sales Document”

Sales Document

330000233

330000234

330000235

330000236

330000237

330000238

 

In order to try to calculate the unique number of sales orders I have a 3rd table: UniqueSalesOrders = VALUES('Orders Invoiced'[Sales Document])

The  “UniqueSalesOrders” table and the ”MinInvoiceDateByOrder” table is linked via “Sales Document”

9 REPLIES 9
some_bih
Super User
Super User

Hi @hgrejsen if possible, share file with sample data and expected output.





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

Proud to be a Super User!






I have not shared such a file before and I dont seem to find an attach file option anywhere....

Hi @hgrejsen ok. I will try in next days to find time and recreate data from data you show above.





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

Proud to be a Super User!






Hi @hgrejsen ok. I will try in next days to find time and recreate data from data you show above.





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

Proud to be a Super User!






I have the numbers in an excel file, so if there is a way to upload it I can do it.

 

thanks

some_bih
Super User
Super User

Hi @hgrejsen great 🙂 "counting" could be last step when model is properly setup according to best practice - this is experince. If in your model, relationships are ok and you have Date table "counting" should be ok.





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

Proud to be a Super User!






Hi, I did make the date Table and linked the date column with the "Invoice Date" in my "Order Invoiced". I then tried to make a simple

Total count = DISTINCTCOUNTNOBLANK('Orders Invoiced'[Sales Document])

 

But it still counts the overall orders im my table/graphs:

 

MonthSum Q IssuesTotal count
Apr251272
Dec711272

 

The next challenge is now that I had the (Minimum) MinInvoicedate and month in a seperate table. But now im not sure how to handle this with the Date Table. 

 

some_bih
Super User
Super User

Hi @hgrejsen " it does not seem like it is only counting those that belongs to the month" you should have Date table to use time intelligence functions in DAX. I hope you have, if not check how to do it with DAX, and mark it to Date table. I hope this help

 https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/ 





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

Proud to be a Super User!






Hi, thx for taking the time to answer. IF im using a Date table will that accept the use of the mindate aswell? I have some sales orders that can be in several months, but it should only "count" in the first month where it is present. I will try to work on your suggestion. Br Helene

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.