topic Re: Dax solution required (how to calculate frequency) in DAX Commands and Tips

Dax solution required (how to calculate frequency)

saad_mce — Fri, 08 Jul 2022 07:13:50 GMT

Hi all,

I am trying to count how many time we have produced certain products in a year or over a period of two years. This is to set the production frequency in ERP system. I have below list of production records.

I want to get this result from a DAX measure.

Criteria for calculation:

1. If a product is produced consecutive workdays, then frequency is still 1.

2. If a product's output is measured multiple times on a same day, frequency is still 1.

3. If same product is produced few days/weeks/months apart, frequency is 2 or more depending on the actual production frequency.

I was trying to use rankx with virtual table, but could not get my head around it.

Any help or suggestion is much appreciated.

Raw Data:

Date	Product	Units Produced
10/02/2022	Product 1	100
10/02/2022	Product 1	100
10/02/2022	Product 2	100
10/02/2022	Product 3	100
11/02/2022	Product 4	100
12/02/2022	Product 8	100
13/02/2022	Product 8	100
14/02/2022	Product 1	100
14/02/2022	Product 2	100
14/02/2022	Product 3	100
15/02/2022	Product 4	100
15/02/2022	Product 8	100
16/02/2022	Product 8	100
16/02/2022	Product 8	100

Output Data:

Products List	Excel Countifs Result	Dax result wanted
Product 1	3	2
Product 2	2	2
Product 3	2	2
Product 4	2	2
Product 8	5	2

Regards,

Abrar

Re: Dax solution required (how to calculate frequency)

tamerj1 — Fri, 08 Jul 2022 08:26:45 GMT

Hi saad_mce
Please refer to the sample file with the solution https://we.tl/t-txANOKHgtj

Actually this solution still need some work to consider working days only but I still don't fully understand your business logic. Like for example if dates are no consecutive but the gap is actually a weekend or holiday shall we count it or not. Please provide a brief description along with holidays table. I will be Little bet more complex but we can produce accurate results.

Count Result = SUMX ( SUMMARIZE ( 'Raw Data', 'Raw Data'[Date] ), VAR CurrentDate = 'Raw Data'[Date] VAR PreviousDates = FILTER ( 'Raw Data', 'Raw Data'[Date] < CurrentDate ) VAR PreviousDate = MAXX ( PreviousDates, 'Raw Data'[Date] ) RETURN IF ( CurrentDate - PreviousDate > 1, 1 ) )

Re: Dax solution required (how to calculate frequency)

saad_mce — Tue, 12 Jul 2022 06:30:08 GMT

Hi tamerj1,

This looks good.

Business Logic: The factory is closed on Saturday and Sunday. If the production of a product continues after a gap of dates due to weekend or holidays, then the frequency is still 1 for that production. The calculation should not count the production after the weekend as a new production frequency.

I have not created a daily calendar in the data model specifying working days, weekends and holidays. My calendar is a monthly calendar showing the number working days and working hours in a month. The file reads the calendar from an excel spreadsheet. The excel spreadsheet deducts the holidays from a holidays table in the spreadsheet. Should I build a daily calendar specifying weekdays and weekends?

Regards,

Abrar

Re: Dax solution required (how to calculate frequency)

tamerj1 — Tue, 12 Jul 2022 08:41:28 GMT

saad_mce
The weekends are fixed. No need to be provided in a seperate table. You just need to provide a list holiday dates, one column table will be enough. It will have only few rows mostly repeated for every year.
However, now I'm facing some trouble in one case. For example of of the Product 8 shifts starts and ends in the same weekend. That makes it difficult to count. We need to igone holidays if there is no record but we need to consider it if there is a record. This is a bet confusing.

Re: Dax solution required (how to calculate frequency)

tamerj1 — Tue, 12 Jul 2022 09:43:52 GMT

Hi saad_mce
Here is the updated solution considering both weekends and holidays https://we.tl/t-2MGgJsKTAQ

I have included a sample holiday table. You just need to update as per your local holidays

Count Result 2 = VAR MaxDate = MAX ( 'Raw Data'[Date] ) VAR MinDate = MIN ( 'Raw Data'[Date] ) VAR T1 = CALENDAR ( MinDate, MaxDate ) VAR T2 = VALUES ( Holidays[Holidays] ) VAR T3 = FILTER ( T1, [Date] IN T2 || WEEKDAY ( [Date], 2 ) IN { 6, 7 } ) VAR T4 = SUMMARIZE ( 'Raw Data', 'Raw Data'[Date] ) VAR T5 = DISTINCT ( UNION ( T3, T4 ) ) RETURN SUMX ( T5, VAR CurrentDate = [Date] VAR PreviousDates = FILTER ( T5, [Date] < CurrentDate ) VAR PreviousDate = MAXX ( PreviousDates, [Date] ) RETURN IF ( CurrentDate - PreviousDate > 1, 1 ) )

Re: Dax solution required (how to calculate frequency)

saad_mce — Wed, 03 Aug 2022 23:10:16 GMT

Hi tamerj1,

Apologies for late reply.

I went on a break from work before you posted the final solution.

Great solution and thanks for your help.

Regards,

Abrar

Re: Dax solution required (how to calculate frequency)

saad_mce — Wed, 28 Sep 2022 06:55:41 GMT

Hi tamerj1

I hope you are well.

It has been a while since we went through this. I am trying to understand the formula better so that I can use this in another model (where I am getting wrong result by using this formula).

Inside the sumx, you are using "VAR CurrentDate = [Date]". Is this [Date] column coming from virtual table T5 or is this from the raw data table?

Regards,

Abrar

Re: Dax solution required (how to calculate frequency)

tamerj1 — Wed, 28 Sep 2022 08:00:02 GMT

Hi saad_mce
This is from the raw data table summarized by date. Meaning the unique date value. You can alternatively use VALUES ( 'Raw Data'[Date] ) or DISTINCT ( 'Raw Data'[Date] ) it should produce the same result.

Re: Dax solution required (how to calculate frequency)

saad_mce — Thu, 29 Sep 2022 03:17:22 GMT

Hi tamerj1

The formula is working fine when the SKU is produced repeatedly on the same date. But if the SKU is produced in different dates in the same year, it giving wrong result (example SKU 10219). I am trying to modify it. Any suggestion will be helpful. You already helped a lot. If you are busy and can not help, thats fine.

Regards,

Abrar