Dax solution required (how to calculate frequency)

[saad_mce]

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

[tamerj1 · Accepted solution]

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
        )
)

[tamerj1 · Accepted solution]

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
            )
    )

[saad_mce]

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

[tamerj1]

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.

[saad_mce]

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

[tamerj1 · Accepted solution]

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
        )
)

[saad_mce]

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

[saad_mce]

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

[tamerj1]

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