Forum Discussion

Frequent Visitor

5 years ago

Solved

Need to Automatically Filter by Week and Time Range

Hello Power BI Community, My current client is asking if we could provide them a calculation in the form of a proportion below: # of clients discharged from Sunday-Sunday 5pm-5pm/ # of tota...

Need Help

v-yalanwu-msft

5 years ago

Hi viwinski7 ,

You could modify the MaxArrival2 column by the following formula:

count =
CALCULATE (
    COUNTROWS ( 'Table' ),
    FILTER (
        ALL ( 'Table' ),
        [ExitDate/Time] = BLANK ()
            || (
                WEEKNUM ( [ExitDate/Time] ) = WEEKNUM ( TODAY () )
                    && (
                        (
                            WEEKDAY ( [ExitDate/Time], 2 ) = 6
                                && TIMEVALUE ( [ExitDate/Time] ) < TIMEVALUE ( "17:00:01" )
                        )
                            || (
                                WEEKDAY ( [ExitDate/Time], 2 ) = 7
                                    && TIMEVALUE ( [ExitDate/Time] ) >= TIMEVALUE ( "17:00:00" )
                            )
                            || ( WEEKDAY ( [ExitDate/Time], 2 ) IN { 1, 2, 3, 4, 5 } )
                    ))))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Need to Automatically Filter by Week and Time Range.pbix62 KB

v-yalanwu-msft

Community Support

5 years ago

Hi , viwinski7 ,

I am not very clear about your question. Based on my understanding, I have created a simple example to calculate the total of discharged or blank on Sunday; if you want to update automatically, you can change WEEKNUM ( [ExitDate/Time] ) = 12 to WEEKNUM ( [ExitDate/Time] ) = WEEKNUM ( TODAY()); as follows:

count =
COUNTROWS (
    FILTER (
        ALL ( 'Table' ),
        WEEKNUM ( [ExitDate/Time] ) = 12
            && (
                [ExitDate/Time] = BLANK ()
                    || WEEKDAY ( [ExitDate/Time], 2 ) = 7
                        && TIME ( HOUR ( [ExitDate/Time] ), MINUTE ( [ExitDate/Time] ), SECOND ( [ExitDate/Time] ) )
                            = TIME ( 17, 0, 0 )
            )
    )
)

The final output is shown below:

If this is still not what you want, please provide me with more details about your table and
your problem or share me with your pbix file after removing sensitive data.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Need to Automatically Filter by Week and Time Range.pbix52 KB