Forum Discussion

saad_mce's avatar
saad_mce
Icon for Helper I rankHelper I
4 years ago
Solved

Dax solution required (how to calculate frequency)

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

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

     

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

8 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    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's avatar
      saad_mce
      Icon for Helper I rankHelper I

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion

        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.

    • saad_mce's avatar
      saad_mce
      Icon for Helper I rankHelper I

      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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    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's avatar
      saad_mce
      Icon for Helper I rankHelper I

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion

        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.