Determining How Many Days Since Last error

[Anonymous]

Hi Everyone,

I have below columns

1.Source,  2. This week Num of days without errors & Week of year column column. i want number of days without error.

In Below column for source CFDI all fives days in week-10, week-9, week-8 there was no error so number of days without error will be 15 but for FEX it has to be 9 because i have received error on day 1 of week 9 so it has to caliculate last 4 days of week9 (which doesn't have any error) and 5 days of week 10 (which doesn't have any error) how can i achive this  
 

[v-yiruan-msft]

Hi @Anonymous ,

I created a sample pbix file(see attachment), please check whether that is what you want.

Best Regards

[Anonymous]

Hi @v-yiruan-msft  Thanks for the reply 
I have date and week columns in my data. Days of Week column represents number of days without error in that week trying with ur measure but i am getting error i mean values i am geeting is 115 instead of 15  

[v-yiruan-msft]

Hi @Anonymous ,

I just add date and week columns in my source data, it works well base on original measures... Could you please provide your sample pbix file with my measure(exclude sensitive data) and the formula if the field Days of Week is from measure or calculated column? By the way, whether include the data from different year? I want to make troubleshooting and find the cause to get the solution base on your scenario. Thank you.

Best Regards

[Anonymous]

@v-yiruan-msft  Thansk for the soultion this worked for me. here i have only one year (2021) values. any idea if i have more that one year of data ...? 

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I updated my sample pbix file(see attachment), please check whether that is what you want.

Measure =

VAR _week =

CALCULATE (

MAX ( 'Table'[Week of Year] ),

FILTER (

'Table',

'Table'[Source] = SELECTEDVALUE ( 'Table'[Source] )

&&'Table'[Year]=SELECTEDVALUE('Table'[Year])

&& 'Table'[Days of week] < 5

)

)

VAR _days =

CALCULATE ( SUM ( 'Table'[Days of week] ) )

VAR _days1 =

CALCULATE (

SUM ( 'Table'[Days of week] ),

FILTER (

'Table',

'Table'[Source] = SELECTEDVALUE ( 'Table'[Source] )

&&'Table'[Year]=SELECTEDVALUE('Table'[Year])

&& 'Table'[Week of Year]

>= _week

)

)

RETURN

IF ( ISBLANK ( _week ), _days, _days1 )

Measure 2 = SUMX(VALUES('Table'[Source]),SUMX(VALUES('Table'[Year]),[Measure]))

Best Regards

[Anonymous]

@v-yiruan-msft Thank you this is working