Forum Discussion

Caitlin_Knox's avatar
Caitlin_Knox
Advocate III
7 years ago

Excluding weekends from performance metrics is

I know several posts have been made about this, and I have tried the strategies present in those threads. However, I'm still having issues. I think my problem is that I have the datekey of my dimensi...
Need Help
Chihiro's avatar
Chihiro
Solution Sage
7 years ago

May be try something like below for calculated column?

=
IF (
    ISBLANK ( [Closed Date] ),
    COUNTROWS (
        FILTER (
            DimDate,
            [Date] >= 'sample data'[Created]
                && [Date] < TODAY ()
                && DimDate[DayOfWeekNumber] <> 7
                && DimDate[DayOfWeekNumber] <> 1
        )
    ),
    COUNTROWS (
        FILTER (
            DimDate,
            [Date] >= 'sample data'[Created]
                && [Date] < 'sample data'[Closed Date]
                && DimDate[DayOfWeekNumber] <> 7
                && DimDate[DayOfWeekNumber] <> 1
        )
    )
)
    + 0

 

 

Caitlin_Knox's avatar
Caitlin_Knox
Advocate III
7 years ago

ChihiroThis is great, and much closer than I have gotten. However, the ouput is a whole number- as in it's not calculating from the date time values of the created and closed. Any ideas on how to get it to be more granular?

  • Chihiro's avatar
    Chihiro
    Solution Sage
    7 years ago

    Hmm, I suppose you can use MOD(Now() - [Created], 1) and MOD([Closed Date] - [Created], 1), and add it back to respective calculation result (i.e True part & False part).