Frequency over range

[Anonymous]

I have an Excel formula that looks for a frequency over a range and when it sees greater than 14 consecutive values over zeo it flags 'Yes'

=IF(SUM(IF(FREQUENCY(IF(D5:Z5>0,COLUMN(D5:Z5)),IF(D5:Z5=0,COLUMN(D5:Z5)))>14,1))>0,"Yes","No")

I use the above formula to see if an employee/contractor works for more than 14 conscutive days

I am looking for a similar DAX expression that will give me a boolean return if 14, greater than zero, consecutive values occour

[Anonymous]

Here is an example. The data for my purpose is from a SQL database and the end result will be in Power BI.

The basic premise is that nobody should work greater than 14 days without one day off. We will use this to allow managers to monitor and correct the behaviour.  

[Anonymous · Accepted solution]

Hi @Anonymous ,

To my knowledge, FREQUENCY() is not available for DAX. So I need some data sample to clarify your scenario.

Please provide me with more details about your table and the expected output or share me with your pbix file after removing sensitive data.

Or since you could successfully add a flag column in Excel, you could directly import it to Power BI instead of creating it in PBI Desktop.

Best Regards,
Eyelyn Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

You can check for over 14 by seeing if every day of the last 15 had a positive value.

It's difficult to write anything specific without more information on what tables/columns/measures you're working with, but maybe something like this:

IsOver14Days =
VAR CurrDay =
    CALCULATE ( SELECTEDVALUE ( DateTable[Date] ) )
RETURN
    SUMX (
        FILTER (
            ALL ( DateTable ),
            DateTable[Date] <= CurrDay
                && DateTable[Date] > CurrDay - 15
        ),
        IF ( [Value] > 0, 1 )
    ) = 15