Forum Discussion

anil_patil65's avatar
anil_patil65
New Member
1 year ago
Solved

Networkdays with filter function

Hi All,

I want to calculate Turn Around Days of the tasks by filtering couple of columns. I am using below formula, but its showing as error. Please help me on this.

 

TAD1 = CALCULATE(FILTER('Drawing & Design Review Tracker', 'Drawing & Design Review Tracker'[Release Status] = "Before Release"&&'Drawing & Design Review Tracker'[Drawing/Design] = "Drawing"),NETWORKDAYS('Drawing & Design Review Tracker'[Received Date],'Drawing & Design Review Tracker'[Delivery Date]))
 
Regards,
Anil Patil
  • anil_patil65's avatar
    anil_patil65
    1 year ago

    FreemanZ ,

    Thanks for your response 🙂

    Now I have slightly modified it and its working fine. I wanted an average value of turn around days on a monthly basis by filtering few columns. Here is the modified version

     

    TAD1 = CALCULATE(AVERAGEX('Drawing & Design Review Tracker',NETWORKDAYS('Drawing & Design Review Tracker'[Received Date],'Drawing & Design Review Tracker'[Delivery Date])),FILTER('Drawing & Design Review Tracker','Drawing & Design Review Tracker'[Drawing/Design]="Drawing"&&'Drawing & Design Review Tracker'[Release Status]="Before Release"))

4 Replies

  • anil_patil65's avatar
    anil_patil65
    New Member
    1 year ago

    FreemanZ ,

    Thanks for your response 🙂

    Now I have slightly modified it and its working fine. I wanted an average value of turn around days on a monthly basis by filtering few columns. Here is the modified version

     

    TAD1 = CALCULATE(AVERAGEX('Drawing & Design Review Tracker',NETWORKDAYS('Drawing & Design Review Tracker'[Received Date],'Drawing & Design Review Tracker'[Delivery Date])),FILTER('Drawing & Design Review Tracker','Drawing & Design Review Tracker'[Drawing/Design]="Drawing"&&'Drawing & Design Review Tracker'[Release Status]="Before Release"))
  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    anil_patil65 , Try using updated measure

     

    DAX
    TAD1 =
    CALCULATE(
    NETWORKDAYS(
    'Drawing & Design Review Tracker'[Received Date],
    'Drawing & Design Review Tracker'[Delivery Date]
    ),
    FILTER(
    'Drawing & Design Review Tracker',
    'Drawing & Design Review Tracker'[Release Status] = "Before Release" &&
    'Drawing & Design Review Tracker'[Drawing/Design] = "Drawing"
    )
    )

    • anil_patil65's avatar
      anil_patil65
      New Member
      1 year ago

      bhanu_gautam ,

       

      If I flip the functions, I can't choose Received date and Delivered Date for Networkdays from the 'Drawing & Design Review Tracker' table.

       

      Regards,

      Anil Patil

      • FreemanZ's avatar
        FreemanZ
        Super User
        1 year ago

        hi anil_patil65 ,

         

        the first argument for CALCULATE should return a value, instead of a table (like the FILTER expression in your original post), that is why you encountered an error. 

         

        CALCULATE can overwrite filter context, so you will not be able to filter the columns as long as you wrap it inside CALCULATE.