Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Determining How Many Days Since Last error

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  
 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Anonymous  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  

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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