"days below target" - Count days under target but resets when target is hit

[Anonymous]

Hi all, 

First post in this community and hope someone can help me with a solution to my problem that I have been dealing with some times now.  Please let me know if more data is needed to solve this issue - like I said it is my first post and wasn't sure how much info I had to give..

I'm trying to create a measure (on a card visual) that counts the number of days our sales team have been under performing (under target) since they hit our budget last. This means that the measure should count the number of days until they hit the target and then the measure should reset to 0 days below target.

I have a measure of total sales however this should only be sumed on daily level and should not be affected when changing the x-axis to months instead of dates. 

I hope someone can help.

Best,

Nico

[OwenAuger · Accepted solution]

Hi Nico,

As a starting point, the measure would looks something like:

# Days below target =
VAR DailyTarget = [Daily Target Value] -- This can be any expression that returns the daily target
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( 'Date'[Date] ),
            [Sales] < DailyTarget
        )
    )
 

This assumes you have a Date table with relationship(s) to relevant tables and marked as a date table, and a [Sales] measure.

I note that in your visual, weekends are excluded. If weekends are not already excluded by a filter in the report, you may need to modify VALUES ( 'Date'[Date] ) to exclude weekends, or apply a filter to the overall expression to exclude weekends.

Does something like this work?

Regards,

Owen