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