topic Re: How to calculate First Time Through for each month - issue with the count. in DAX Commands and Tips

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

hgrejsen — Tue, 06 Jun 2023 07:09:16 GMT

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:

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”

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

some_bih — Tue, 06 Jun 2023 09:48:00 GMT

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/

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

hgrejsen — Tue, 06 Jun 2023 10:50:59 GMT

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

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

some_bih — Tue, 06 Jun 2023 11:08:05 GMT

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.

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

hgrejsen — Wed, 07 Jun 2023 05:52:11 GMT

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:

Month	Sum Q Issues	Total count
Apr	25	1272
Dec	71	1272

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.

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

some_bih — Wed, 07 Jun 2023 06:49:00 GMT

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

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

hgrejsen — Wed, 07 Jun 2023 08:06:08 GMT

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

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

some_bih — Wed, 07 Jun 2023 08:26:28 GMT

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

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

hgrejsen — Wed, 07 Jun 2023 08:41:21 GMT

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

thanks

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

some_bih — Sat, 10 Jun 2023 09:14:25 GMT

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