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 prod...
  • tamerj1's avatar
    4 years ago

    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's avatar
    4 years ago

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