Forum Discussion

rachaelwalker's avatar
rachaelwalker
Resolver III
6 years ago
Solved

Aggregating Quick Measures

I created a quick measure to show the sum of business workdays between two dates. In another visual, I want to show the average of workdays per project manager, however; it does not give me an option...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi rachaelwalker ，

    Please try to update the existed calculated column "Warranty to Sent Invoice" as below to get the working days between two days:

     

    Warranty to Sent Invoice =
IF (
    ISBLANK ( 'Projects'[WarrantyStart] ) || ISBLANK ( 'Projects'[Invoice Sent] ),
    0,
    CALCULATE (
        COUNTROWS ( 'Query2' ),
        FILTER ( 'Query2', WEEKDAY ( 'Query2'[Date], 2 ) < 6 ),
        DATESBETWEEN (
            'Query2'[Date],
            'Projects'[WarrantyStart],
            Projects[Invoice Sent]
        )
    )
)

     

    Best Regards

    Rena

rachaelwalker's avatar
rachaelwalker
Resolver III
6 years ago

I believe these solutions would work if my measure was a calculated column. I tried using a calculated column instead, but it is returning all zeros. 

 

 

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi rachaelwalker ,

    The data of table visual in your report are all from table "Projects"? Could you please provide some sample data in this table? It is better if you can provide your pbix file. Then I can make troubleshooting based on your report file, it will be very helpful to find the root cause of problem. Thank you.

    Best Regards

    Rena

    • rachaelwalker's avatar
      rachaelwalker
      Resolver III
      6 years ago

      For security reasons, I will send it directly to you

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi rachaelwalker ，

        Please try to update the existed calculated column "Warranty to Sent Invoice" as below to get the working days between two days:

         

        Warranty to Sent Invoice =
IF (
    ISBLANK ( 'Projects'[WarrantyStart] ) || ISBLANK ( 'Projects'[Invoice Sent] ),
    0,
    CALCULATE (
        COUNTROWS ( 'Query2' ),
        FILTER ( 'Query2', WEEKDAY ( 'Query2'[Date], 2 ) < 6 ),
        DATESBETWEEN (
            'Query2'[Date],
            'Projects'[WarrantyStart],
            Projects[Invoice Sent]
        )
    )
)

         

        Best Regards

        Rena