Consecutive Days Win/Loss Excluding Weekends

[Anonymous]

Hi all,

I'm trying to present a measure in my report that shows the number of consecutive days (excluding weekends) that the Observed has been above the Expected (Bad) or below the Expected (Good - I want the Observed to be lower than Expected).

About the model: I currently have a list of actual observations by date, linked to a Dates table (called 'Time Period 1').  That Date table is linked to a list of dates with the total we'd expect each day (called '2018 Predicted Calculations').  I've already created a column on the Dates tables that identifies whether it's weekday or weekend (called [Weekday or Weekend]).

Here's what I currently have in my report (other than the last column, which is what I want to create):

I've borrowed some DAX from another post that almost manages to calculate what I need but it includes weekends (i.e. if the WinMeasure/Lossmeasure is zero at the weekend, the consecutive count stops, but I want it to carry over).  I don't want to exclude weekends from my table entirely as we may sometimes have activity on a weekend but the expected will always be zero so I want the measure to ignore weekends.

Here's the DAX for the WinMeasure:

WinMeasure = if(SELECTEDVALUE('Time Period 1'[Weekday or Weekend])= "Weekday" ,   --ignore weekends
if([Observed - expected]>0,0,1),BLANK())

the LossMeasure works on the same principle:

LossMeasure = if(SELECTEDVALUE('Time Period 1'[Weekday or Weekend])= "Weekday" ,   --ignore weekends
if([Observed - expected]>0,1,0),BLANK())

And the Consecutive Win/Loss measures work like this at the moment:

Consecutive Loss = 
SWITCH (
    TRUE (),
    ([LossMeasure]= 0 || [LossMeasure]= blank()), 0,
    CALCULATE (
        COUNTROWS ( '2018 Predicted calculations' ),
        FILTER (
            ALL ( '2018 Predicted calculations'),
            ([LossMeasure]= 0 || [LossMeasure]= blank())
                &&  '2018 Predicted calculations'[Date] < SELECTEDVALUE( '2018 Predicted calculations'[Date] )
        )
    )
        = 0, CALCULATE (
        SUMX('2018 Predicted calculations',[LossMeasure]),
        FILTER (
            ALL ( '2018 Predicted calculations'),
             '2018 Predicted calculations'[Date] <=SELECTEDVALUE( '2018 Predicted calculations'[Date] )
        ),
Filter ('Time Period 1','Time Period 1'[Weekday or Weekend] = "Weekday")
    ),
    CALCULATE (
        SUMX ('2018 Predicted calculations',[LossMeasure]),
        FILTER (
            ALL ( '2018 Predicted calculations'),
                 '2018 Predicted calculations'[Date] 
                    > CALCULATE (
                        MAX (      '2018 Predicted calculations'[Date]  ),
                        FILTER (
                            ALL (      '2018 Predicted calculations'),
                            ([LossMeasure]= 0 || [LossMeasure]= blank())
                                && '2018 Predicted calculations'[Date]  < SELECTEDVALUE( '2018 Predicted calculations'[Date] )
                        )
                    )
                &&  '2018 Predicted calculations'[Date]  <= SELECTEDVALUE( '2018 Predicted calculations'[Date] )
        )
    )
)

But as I said this doesn't carry over the weekends so outputs the cell highlighted in Red in the attached table rather than the Yellow.  Basically, from the attached table we can see that the Observed has been over the Expected for 4 working days, so I want to display 4, not 1 under Monday 10th.

Hope that makes sense!

Thanks in advance.

[v-danhe-msft]

Hi @Anonymous,

From your picture, I guess you want to calculate the cummulative sum of [LossMeasure], if so, you could refer to below formula:

Measure = 
CALCULATE (
    SUMX (
        '2018 Predicted calculations',
        '2018 Predicted calculations'[LossMeasure]
    ),
    FILTER (
        ALL ( '2018 Predicted calculations' ),
        '2018 Predicted calculations'[Date]
            <= MAX ( '2018 Predicted calculations'[Date] )
    )
)

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous]

hiya @v-danhe-msft

Thanks for your response.  Unfortunately that doesn't quite do what I want (I've just re-read my question and realised I wasn't clear so my fault!).   I need the Consecutive Loss measure to reset if there's a win (and vice versa), whereas this will keep counting.  

You'll see in the picture I attached to my original question there were two Wins in a row but when there was a loss, the Consecuive Win measure reset to zero.  I'm only interested in consecutive working days with a Win/Loss.  Does that make sense? 

So, looking at my table again, if the 11th was a Win, the Consecutive Loss Measure would be 0 that day and the Consecutive Win would be 1.

Thanks,

Holly