Forum Discussion
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...
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))RETURNIF ( ISBLANK ( _week ), _days, _days1 )Measure 2 = SUMX(VALUES('Table'[Source]),SUMX(VALUES('Table'[Year]),[Measure]))Best Regards
Anonymous 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 ...?
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 Thank you this is working