Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Consecutive Days Win/Loss Excluding Weekends

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...
Need Help
v-danhe-msft's avatar
v-danhe-msft
Microsoft Employee
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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